+ Reply to Thread
Results 1 to 7 of 7

Macro Ending Early - When Using Range.Value on some macros?

Hybrid View

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    USA
    MS-Off Ver
    2003, 2010, 2013, 2016
    Posts
    12

    Macro Ending Early - When Using Range.Value on some macros?

    For some reason my code is exiting early, and I really have no idea why. I'm triggering the PasteRangeMacro sub via the PasteDay1 sub (among others) and it's really annoying that the macro isn't finishing, among other reasons because the code doesn't re-enable events and reset the calculation status.
    It gives the "test0" debug print, and triggers the StartCell.Resize(DayRows,7).Value line, but does not trigger any of the following lines. I use Range.Value in other macros without this problem, so I'm stuck. Any ideas? Thank you so much.

    I'm using the latest version of Excel with a file format of .xlsm on a Windows 10 computer.

    Sub PasteRangeMacro(StartCell As Range)
        Application.EnableEvents = False
            
        If StoredCopyCell Is Nothing Then GoTo ErrorMessage
        OldCalculationMode = Application.Calculation
        Application.Calculation = xlCalculationManual
        Dim CopyStart As Range
        Set CopyStart = StoredCopyCell
        Debug.Print "test0"
        StartCell.Resize(DayRows, 7).Value = CopyStart.Resize(DayRows, 7).Value
        Debug.Print StartCell.Offset(0, 9).Resize(DayRows, 1).Address
        StartCell.Offset(0, 9).Resize(DayRows, 1).Value = CopyStart.Offset(0, 9).Resize(DayRows, 1).Value
        StartCell.Offset(0, 12).Resize(DayRows, 1).Value = CopyStart.Offset(0, 12).Resize(DayRows, 1).Value
        StartCell.Offset(0, 15).Resize(DayRows, 1).Value = CopyStart.Offset(0, 15).Resize(DayRows, 1).Value
        Debug.Print "test"
        Application.EnableEvents = True
        Application.Calculation = OldCalculationMode
        ActiveSheet.Calculate
        Exit Sub
     
    ErrorMessage:
        Application.EnableEvents = True
        MsgBox "Error - Please use a Copy button before pressing a Paste button."
    End Sub
    
    Sub PasteDay1()
        Dim StartCell As Range
        Set StartCell = Range("E5")
        Call PasteRangeMacro(StartCell)
    End Sub
    Last edited by bkm2016; 05-28-2017 at 04:41 PM. Reason: marking solved

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Macro Ending Early - When Using Range.Value on some macros?

    The Range variable CopyStart is never assigned to a worksheet range. It is set to the value of a variable called StoredCopyCell but that is never initialised either...

    Also, the code, as posted, will not compile as StoredCopyRange is not declared and you cannot check an undeclared variable for 'Nothing'
        If StoredCopyCell Is Nothing Then GoTo ErrorMessage

  3. #3
    Registered User
    Join Date
    06-05-2013
    Location
    USA
    MS-Off Ver
    2003, 2010, 2013, 2016
    Posts
    12

    Re: Macro Ending Early - When Using Range.Value on some macros?

    Sorry, they are declared as public variables in another macro.

    Give me a few minutes. I'll upload a modified version of my file.

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Macro Ending Early - When Using Range.Value on some macros?

    Not being pedantic - but you cannot declare a Public Variable in another macro... It must be in the General Declarations section of a standard code module. You set the value in a procedure, though.

  5. #5
    Registered User
    Join Date
    06-05-2013
    Location
    USA
    MS-Off Ver
    2003, 2010, 2013, 2016
    Posts
    12

    Re: Macro Ending Early - When Using Range.Value on some macros?

    You know what I meant, that's right. Thanks for helping other people, since I wasn't clear

  6. #6
    Registered User
    Join Date
    06-05-2013
    Location
    USA
    MS-Off Ver
    2003, 2010, 2013, 2016
    Posts
    12

    Re: Macro Ending Early - When Using Range.Value on some macros?

    The file is too large to upload but here are the two relevant modules:
    Public StoredCopyCell As Range
    Public CopySpecificRange As Range
    Public CopyWeekRange As Range
    
    Sub CopyRangeMacro(StartCell As Range)
        StartCell.Resize(DayRows, 16).Select
        StartCell.Resize(DayRows, 16).Copy
        Set StoredCopyCell = StartCell
    End Sub
    
    Sub CopyDay1()
        Dim StartCell As Range
        Set StartCell = Range("E5")
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay2()
        Dim StartCell As Range
        Set StartCell = Range("E" & 1 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay3()
        Dim StartCell As Range
        Set StartCell = Range("E" & 2 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay4()
        Dim StartCell As Range
        Set StartCell = Range("E" & 3 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay5()
        Dim StartCell As Range
        Set StartCell = Range("E" & 4 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay6()
        Dim StartCell As Range
        Set StartCell = Range("E" & 5 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay7()
        Dim StartCell As Range
        Set StartCell = Range("E" & 6 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopySpecificRows()
    Dim NumRows As Integer
    Dim StartCell As Range
    
    On Error GoTo ErrorMessage
    Set CopySpecificRange = Application.InputBox(Title:="Rows to Copy", Prompt:="Select range with all the rows you want to copy", Type:=8)
        
        NumRows = CopySpecificRange.Rows.Count
        Set StartCell = Cells(CopySpecificRange.Cells(1, 1).Row, 5)
        StartCell.Resize(NumRows, 16).Select
        StartCell.Resize(NumRows, 16).Copy
        
    Set CopySpecificRange = StartCell.Resize(NumRows, 16)
    
    Exit Sub
    
    ErrorMessage:
    MsgBox "Please select a range"
    End Sub
    
    
    Sub CopyWeek()
    
    Dim NumRows As Integer
    Dim StartCell As Range
    Set StartCell = Range("E5")
    NumRows = (7 * (DayRows + 2) - 2)
    StartCell.Resize(NumRows, 16).Select
    StartCell.Resize(NumRows, 16).Copy
    Set CopyWeekRange = StartCell.Resize(NumRows, 16)
    
    End Sub
    Sub PasteRangeMacro(StartCell As Range)
        Application.EnableEvents = False
            
        If StoredCopyCell Is Nothing Then GoTo ErrorMessage
        OldCalculationMode = Application.Calculation
        Application.Calculation = xlCalculationManual
        Dim CopyStart As Range
        Set CopyStart = StoredCopyCell
        Debug.Print "test0"
        StartCell.Resize(DayRows, 7).Value = CopyStart.Resize(DayRows, 7).Value
        Debug.Print StartCell.Offset(0, 9).Resize(DayRows, 1).Address
        StartCell.Offset(0, 9).Resize(DayRows, 1).Value = CopyStart.Offset(0, 9).Resize(DayRows, 1).Value
        StartCell.Offset(0, 12).Resize(DayRows, 1).Value = CopyStart.Offset(0, 12).Resize(DayRows, 1).Value
        StartCell.Offset(0, 15).Resize(DayRows, 1).Value = CopyStart.Offset(0, 15).Resize(DayRows, 1).Value
        Debug.Print "test"
        Application.EnableEvents = True
        Application.Calculation = OldCalculationMode
        ActiveSheet.Calculate
        Exit Sub
     
    ErrorMessage:
        Application.EnableEvents = True
        MsgBox "Error - Please use a Copy button before pressing a Paste button."
    End Sub
    
    Sub PasteDay1()
        Dim StartCell As Range
        Set StartCell = Range("E5")
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay2()
        Dim StartCell As Range
        Set StartCell = Range("E" & 1 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay3()
        Dim StartCell As Range
        Set StartCell = Range("E" & 2 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay4()
        Dim StartCell As Range
        Set StartCell = Range("E" & 3 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay5()
        Dim StartCell As Range
        Set StartCell = Range("E" & 4 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay6()
        Dim StartCell As Range
        Set StartCell = Range("E" & 5 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay7()
        Dim StartCell As Range
        Set StartCell = Range("E" & 6 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteSpecificRange()
        Application.EnableEvents = False
            
        If CopySpecificRange Is Nothing Then GoTo ErrorMessage
        Dim CopyStart As Range
        Dim NumRows As Integer
        Set CopyStart = CopySpecificRange.Cells(1, 1)
        NumRows = CopySpecificRange.Rows.Count
        
        On Error GoTo ErrorMessage
        Set StartCell = Cells(Selection.Cells(1, 1).Row, 5)
        On Error GoTo 0
        
        StartCell.Resize(NumRows, 7).Value = CopyStart.Resize(NumRows, 7).Value
        StartCell.Offset(0, 9).Resize(NumRows, 1).Value = CopyStart.Offset(0, 9).Resize(NumRows, 1).Value
        StartCell.Offset(0, 12).Resize(NumRows, 1).Value = CopyStart.Offset(0, 12).Resize(NumRows, 1).Value
        StartCell.Offset(0, 15).Resize(NumRows, 1).Value = CopyStart.Offset(0, 15).Resize(NumRows, 1).Value
    
        Application.EnableEvents = True
        ActiveSheet.Calculate
        Exit Sub
     
    ErrorMessage:
        Application.EnableEvents = True
        MsgBox "Error - Please use the Copy Specific Range button before pressing a Paste button. Please Select 1 cell before Clicking Paste"
    
    End Sub
    
    Sub PasteWeek()
    
     Application.EnableEvents = False
            
        If CopyWeekRange Is Nothing Then GoTo ErrorMessage
        Dim CopyStart As Range
        Dim NumRows As Integer
        Set CopyStart = CopyWeekRange.Cells(1, 1)
        NumRows = CopyWeekRange.Rows.Count
        
        On Error GoTo ErrorMessage
        Set StartCell = Range("E5")
        On Error GoTo 0
    
        StartCell.Resize(NumRows, 7).Value = CopyStart.Resize(NumRows, 7).Value
        Debug.Print "TEST1"
        StartCell.Offset(0, 9).Resize(NumRows).Value = CopyStart.Offset(0, 9).Resize(NumRows).Value
        Debug.Print "TEST2"
        StartCell.Offset(0, 12).Resize(NumRows).Value = CopyStart.Offset(0, 12).Resize(NumRows).Value
        Debug.Print "TEST3"
        StartCell.Offset(0, 15).Resize(NumRows).Value = CopyStart.Offset(0, 15).Resize(NumRows).Value
        
        
        Debug.Print "TEST"
        Application.EnableEvents = True
        ActiveSheet.Calculate
        Exit Sub
     
    ErrorMessage:
        Application.EnableEvents = True
        MsgBox "Error - Please use the Copy Week button before pressing a Paste button."
    
    End Sub
     Public Const DayRows As Integer = 10
    Last edited by bkm2016; 05-28-2017 at 02:52 PM.

  7. #7
    Registered User
    Join Date
    06-05-2013
    Location
    USA
    MS-Off Ver
    2003, 2010, 2013, 2016
    Posts
    12

    Re: Macro Ending Early - When Using Range.Value on some macros?

    I'm not really sure what I did, but I'm not having the problem any longer. Maybe adding screen updating helped, but I'm not sure. Anyway, just in case someone else has a similar problem, my new code is below.

    Public StoredCopyCell As Range
    Public CopySpecificRange As Range
    Public CopyWeekRange As Range
    
    Sub CopyRangeMacro(StartCell As Range)
        StartCell.Resize(DayRows, 16).Select
        StartCell.Resize(DayRows, 16).Copy
        Set StoredCopyCell = StartCell
    End Sub
    
    Sub CopyDay1()
        Dim StartCell As Range
        Set StartCell = Range("E5")
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay2()
        Dim StartCell As Range
        Set StartCell = Range("E" & 1 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay3()
        Dim StartCell As Range
        Set StartCell = Range("E" & 2 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay4()
        Dim StartCell As Range
        Set StartCell = Range("E" & 3 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay5()
        Dim StartCell As Range
        Set StartCell = Range("E" & 4 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay6()
        Dim StartCell As Range
        Set StartCell = Range("E" & 5 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopyDay7()
        Dim StartCell As Range
        Set StartCell = Range("E" & 6 * (DayRows + 2) + 5)
        Call CopyRangeMacro(StartCell)
    End Sub
    
    Sub CopySpecificRows()
    Dim NumRows As Integer
    Dim StartCell As Range
    
    On Error GoTo ErrorMessage
    Set CopySpecificRange = Application.InputBox(Title:="Rows to Copy", Prompt:="Select range with all the rows you want to copy", Type:=8)
        
        NumRows = CopySpecificRange.Rows.Count
        Set StartCell = Cells(CopySpecificRange.Cells(1, 1).Row, 5)
        StartCell.Resize(NumRows, 16).Select
        StartCell.Resize(NumRows, 16).Copy
        
    Set CopySpecificRange = StartCell.Resize(NumRows, 16)
    
    Exit Sub
    
    ErrorMessage:
    MsgBox "Please select a range"
    End Sub
    
    
    Sub CopyWeek()
    
    Dim NumRows As Integer
    Dim StartCell As Range
    Set StartCell = Range("E5")
    NumRows = (7 * (DayRows + 2) - 2)
    StartCell.Resize(NumRows, 16).Select
    StartCell.Resize(NumRows, 16).Copy
    Set CopyWeekRange = StartCell.Resize(NumRows, 16)
    
    End Sub

    Sub PasteRangeMacro(StartCell As Range)
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        OldCalculationMode = Application.Calculation
        Application.Calculation = xlCalculationManual
        
        If StoredCopyCell Is Nothing Then GoTo ErrorMessage
        Dim CopyStart As Range
        Set CopyStart = StoredCopyCell
        StartCell.Resize(DayRows, 7).Value = CopyStart.Resize(DayRows, 7).Value
    
        StartCell.Offset(0, 9).Resize(DayRows, 1).Value = CopyStart.Offset(0, 9).Resize(DayRows, 1).Value
        StartCell.Offset(0, 12).Resize(DayRows, 1).Value = CopyStart.Offset(0, 12).Resize(DayRows, 1).Value
        StartCell.Offset(0, 15).Resize(DayRows, 1).Value = CopyStart.Offset(0, 15).Resize(DayRows, 1).Value
        
        Application.EnableEvents = True
        Application.Calculation = OldCalculationMode
        ActiveSheet.Calculate
        Application.ScreenUpdating = True
        Exit Sub
     
    ErrorMessage:
        Application.Calculation = OldCalculationMode
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        MsgBox "Error - Please use a Copy button before pressing a Paste button."
    End Sub
    
    Sub PasteDay1()
        Dim StartCell As Range
        Set StartCell = Range("E5")
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay2()
        Dim StartCell As Range
        Set StartCell = Range("E" & 1 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay3()
        Dim StartCell As Range
        Set StartCell = Range("E" & 2 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay4()
        Dim StartCell As Range
        Set StartCell = Range("E" & 3 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay5()
        Dim StartCell As Range
        Set StartCell = Range("E" & 4 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay6()
        Dim StartCell As Range
        Set StartCell = Range("E" & 5 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteDay7()
        Dim StartCell As Range
        Set StartCell = Range("E" & 6 * (DayRows + 2) + 5)
        Call PasteRangeMacro(StartCell)
    End Sub
    
    Sub PasteSpecificRange()
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        OldCalculationMode = Application.Calculation
        Application.Calculation = xlCalculationManual
            
        If CopySpecificRange Is Nothing Then GoTo ErrorMessage
        Dim CopyStart As Range
        Dim NumRows As Integer
        Set CopyStart = CopySpecificRange.Cells(1, 1)
        NumRows = CopySpecificRange.Rows.Count
        
        On Error GoTo ErrorMessage
        Set StartCell = Cells(Selection.Cells(1, 1).Row, 5)
        On Error GoTo 0
        
        StartCell.Resize(NumRows, 7).Value = CopyStart.Resize(NumRows, 7).Value
        StartCell.Offset(0, 9).Resize(NumRows, 1).Value = CopyStart.Offset(0, 9).Resize(NumRows, 1).Value
        StartCell.Offset(0, 12).Resize(NumRows, 1).Value = CopyStart.Offset(0, 12).Resize(NumRows, 1).Value
        StartCell.Offset(0, 15).Resize(NumRows, 1).Value = CopyStart.Offset(0, 15).Resize(NumRows, 1).Value
    
        Application.EnableEvents = True
        Application.Calculation = OldCalculationMode
        ActiveSheet.Calculate
        Application.ScreenUpdating = True
        Exit Sub
     
    ErrorMessage:
        Application.Calculation = OldCalculationMode
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        MsgBox "Error - Please use the Copy Specific Range button before pressing a Paste button. Please Select 1 cell before Clicking Paste"
    
    End Sub
    
    Sub PasteWeek()
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        OldCalculationMode = Application.Calculation
        Application.Calculation = xlCalculationManual
            
        If CopyWeekRange Is Nothing Then GoTo ErrorMessage
        Dim CopyStart As Range
        Dim NumRows As Integer
        Set CopyStart = CopyWeekRange.Cells(1, 1)
        NumRows = CopyWeekRange.Rows.Count
        
        On Error GoTo ErrorMessage
        Set StartCell = Range("E5")
        On Error GoTo 0
    
        StartCell.Resize(NumRows, 7).Value = CopyStart.Resize(NumRows, 7).Value
        StartCell.Offset(0, 9).Resize(NumRows).Value = CopyStart.Offset(0, 9).Resize(NumRows).Value
        StartCell.Offset(0, 12).Resize(NumRows).Value = CopyStart.Offset(0, 12).Resize(NumRows).Value
        StartCell.Offset(0, 15).Resize(NumRows).Value = CopyStart.Offset(0, 15).Resize(NumRows).Value
        
        
        Application.EnableEvents = True
        Application.Calculation = OldCalculationMode
        ActiveSheet.Calculate
        Application.ScreenUpdating = True
        Exit Sub
     
    ErrorMessage:
        Application.Calculation = OldCalculationMode
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        MsgBox "Error - Please use the Copy Week button before pressing a Paste button."
    
    End Sub
    Public Const DayRows As Integer = 10

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Ending Macro - Variable Range
    By Austex_egger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-22-2013, 02:52 PM
  2. Need help identifying a value range for ending a Loop Until
    By erik1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2013, 05:19 AM
  3. [SOLVED] Macro stops too early - Help!
    By cossie2k in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-25-2012, 10:15 AM
  4. Inserting text based on a predetermined criteria early in the macro
    By MonkeyFlyer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-30-2012, 06:06 PM
  5. determine begining row,col and ending row,col of variable range
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 07-17-2011, 08:09 PM
  6. Ending macro early
    By TedH in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-20-2009, 11:45 AM
  7. Ending Macros
    By lj123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-12-2008, 01:10 PM
  8. [SOLVED] Ending a macro early conditionally on one cell being blank
    By Rokuro kubi in forum Excel General
    Replies: 3
    Last Post: 05-26-2006, 09:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1