Looping through all sheets, getting error at end

Question

So I have a workbook with a master sheet and subordinate sheets and among other things (hence the choice = 7 thing) it calculates the totals for every row and every column of the data portion of the sheet as well as the total for all data. I have this part working but then when I went on to the next step/option of comparing the total of the master sheet to the sum of the totals of the subordinate sheets (with the addition of recalculating the totals of each sheet in case of changes or one wasn't filled out etc) I run into an error.

It executes the totals functionality well on every sheet and then it seems to run into an issue at the end of the loop. Not every sheet in this workbook is a data sheet and thus does not adhere to the specific layout the others are. Since these are not in any particular order, I have in my code to just skip them. It does this by intentionally catching the error that arises from working with something not adhering to the layout of the others and then skipping to the bottom of the loop.

At the moment I only have one of these extra sheets and I tried taking it out but it still gets an error near the end of the loop. The weird thing is that even though I have an error handling, it doesn't seem to be caught by it. k here is the sum of the subordinate totals, Position is just a max value (I wanted to make use of all my variables even if they weren't important in other functionalities), any odd numbers like MastLastRow - 6 is to account for the labels and other parts of the sheet that are not the data portion, and the flag part is making sure that someone didn't accidentally delete data. While I have multiple lines where I GoTo ErrCatch7: these just help me skip to the bottom and the only error that should come up anywhere in the code is when hitting an extra sheet. If you need any more possible clarification as to what in the code is doing what or need me to post a picture showing the spreadsheet let me know

ElseIf Fchoice = "7" Then
                k = 0
                Application.Calculation = xlCalculationManual
                Application.ScreenUpdating = False
                Application.DisplayStatusBar = False
                On Error GoTo ErrCatch7:
                MastLastRow = Sheets("Master").Range("A7").CurrentRegion.Rows.Count
                For Each WSCount In Worksheets
                    If WSCount.Name = "Master" Then
                        MastLastCol = Sheets("Master").Range("A5").CurrentRegion.Columns.Count
                        If Sheets("Master").Range("C7").Offset(MastLastRow - 7, 0) = "" Then
                            Sheets("Master").Range("C7").Offset(MastLastRow - 7, 0).EntireRow.ClearContents
                        End If
                        MastLastRow = Sheets("Master").Range("A7").CurrentRegion.Rows.Count
                        j = -1
                        i = 0
                        Set TempM = Range("H7")
                        Do While i < MastLastRow - 6
                            Set TempP = Range(TempM.Offset(0, 1), TempM.Offset(0, MastLastCol))
                            TempM.Value = Application.Sum(TempP)
                            Set TempM = TempM.Offset(1, 0)
                            i = i + 1
                        Loop

                        Do While j < MastLastCol - 8
                            Set TempP = Sheets("Master").Range(TempM.Offset(-1, 0), Sheets("Master").Range("I7").Offset(0, j))
                            TempM.Value = Application.Sum(TempP)
                            Set TempM = TempM.Offset(0, 1)
                            j = j + 1
                        Loop

                        Sheets("Master").Range("H7").Offset(MastLastRow - 6, 0).Font.ColorIndex = 6
                        Sheets("Master").Range("H7").Offset(MastLastRow - 6, 0).Interior.Color = RGB(0, 0, 255)
                        Sheets("Master").Range("H7").Offset(MastLastRow - 6, -1).Value = "Sheet Total"
                        GoTo ErrCatch7:
                    Else
                        Set MastData = Sheets("Master").Range("A7")
                        Set SubordData = WSCount.Range("A7")
                        SubordLastRow = WSCount.Range("A7").CurrentRegion.Rows.Count
                        SubordLastCol = WSCount.Range("A5").CurrentRegion.Columns.Count
                        If WSCount.Range("C7").Offset(SubordLastRow - 7, 0) = "" Then
                            WSCount.Range("C7").Offset(SubordLastRow - 7, 0).EntireRow.ClearContents
                        End If
                        SubordLastRow = WSCount.Range("A7").CurrentRegion.Rows.Count
                        Flag = True
                        If MastLastRow > SubordLastRow Then
                            Position = MastLastRow
                        Else
                            Position = SubordLastRow
                        End If

                        'Check if tasks in subordinate sheet have been mistakenly erased or misformated
                        Do While i < Position And j < Position
                            If MastData.Offset(i, 0).Value = SubordData.Offset(j, 0).Value Then
                                i = i + 1
                                j = j + 1
                            ElseIf MastData.Offset(i, 0).Value = SubordData.Offset(j, 0).Value And MastData.Offset(i, 0).Value = vbNullString Then
                                i = i + 1
                                j = j + 1
                            ElseIf MastData.Offset(i, 0).Value = vbNullString And SubordData.Offset(j, 0).Value <> vbNullString Then
                                i = i + 1
                            ElseIf SubordData.Offset(j, 0).Value = vbNullString And MastData.Offset(i, 0).Value <> vbNullString Then
                                j = j + 1
                            Else
                                Flag = False
                                Exit Do
                            End If
                        Loop
                        If Flag = False Then
                            MsgBox (OrgName + " appears to be missing tasks. This sheet will be ignored. Check for mistakes and compare totals later")
                            GoTo ErrCatch7:
                        End If
                        j = -1
                        i = 0
                        Set TempM = WSCount.Range("H7")
                        Do While i < SubordLastRow - 6
                            Set TempP = WSCount.Range(TempM.Offset(0, 1), TempM.Offset(0, SubordLastCol))
                            TempM.Value = Application.Sum(TempP)
                            Set TempM = TempM.Offset(1, 0)
                            i = i + 1
                        Loop

                        Do While j < SubordLastCol - 8
                            Set TempP = WSCount.Range(TempM.Offset(-1, 0), WSCount.Range("I7").Offset(0, j))
                            TempM.Value = Application.Sum(TempP)
                            Set TempM = TempM.Offset(0, 1)
                            j = j + 1
                        Loop

                        WSCount.Range("H7").Offset(SubordLastRow - 6, 0).Font.ColorIndex = 6
                        WSCount.Range("H7").Offset(SubordLastRow - 6, 0).Interior.Color = RGB(0, 0, 255)
                        WSCount.Range("H7").Offset(SubordLastRow - 6, -1).Value = "Sheet Total"
                    End If
                    k = k + WSCount.Range("H7").Offset(SubordLastRow - 6, 0).Value
    ErrCatch7:
                Next
                Application.Calculation = xlCalculationAutomatic
                Application.ScreenUpdating = True
                Application.DisplayStatusBar = True
                MsgBox ("Master sheet hourly total = " + Sheets("Master").Range("H7").Offset(SubordLastRow - 6, 0).Value + ": Sum of subordinate sheets = " + k)

Answer 1

I believe your problem might be because you are trying to use ErrCatch7 as an error handler.

I suggest you replace "On Error GoTo ErrCatch7" with "On Error GoTo ErrCatch7Err", then include a section at the very end of your procedure (after an Exit Sub or Exit Function) which says:

ErrCatch7Err:
    Resume ErrCatch7

Edit: Based on what Maciej Los just said, it is also a problem that your error handler is enabled while outside the For Next loop as well as inside it. I would recommend that you use

    ...
    For Each WSCount In Worksheets
        On Error GoTo ErrCatch7Err
        ...
        ... (other code, including "GoTo ErrCatch7" statements if needed)
        ...
ErrCatch7:
    Next
    On Error GoTo 0
    ...
    Exit Sub
ErrCatch7Err:
    Resume ErrCatch7

If you want an error handler to cover errors in the

MastLastRow = Sheets("Master").Range("A7").CurrentRegion.Rows.Count

statement which occurs before your loop starts, use a different handler. (You would want different actions to be performed in that case anyway, so using the same handler makes no real sense.)

As an example of the difference between using ErrCatch7 as the target of the On Error condition, and using a separate label as the target, consider the following two code samples:

Sample 1:

Sub TestMe()
    On Error GoTo ErrCatch7
    For i = 1 To 10
        MsgBox i
        j = i / 0
        MsgBox j
ErrCatch7:
    Next i
End Sub

Sample 2:

Sub TestMe()
    On Error GoTo ErrCatch7
    For i = 1 To 10
        MsgBox i
        j = i / 0
        MsgBox j
ErrCatch7:
    Next i
    Exit Sub
ErrCatch7Err:
    Resume ErrCatch7
End Sub

Sample 1 will display the number 1, then (while still handling the "1 / 0" error) 2, then (while attempting "2 / 0") give a division by zero error.

Sample 2 will display all the numbers from 1 to 10.

Answer 2

As answered here and here:

With the code as shown, you're actually still considered to be within the error handling routine when you strike the next statement.

That means that subsequent error handlers are not allowed until you resume from the current one.

A better architecture in your case would be something like:

        'before loop
        On Error GoTo ErrCatch7
        MastLastRow = Sheets("Master").Range("A7").CurrentRegion.Rows.Count
        For Each WSCount In Worksheets
        ....
   NextWorksheet:
        Next
        ' the rest of your code

   ErrCatch7:
        Resume NextWorksheet

As also already mentioned in the answers above, you can find a great explanation about the problem in this site

Answer 3

MSDN documentation states:

You jumped into the middle of a For...Next loop. Remove the jump into the loop. Placing labels inside a For...Next loop isn't recommended.

The issue is here:

           'before loop
            On Error GoTo ErrCatch7:
            MastLastRow = Sheets("Master").Range("A7").CurrentRegion.Rows.Count
            For Each WSCount In Worksheets
            ....
            'jump into the loop is here
       ErrCatch7:
            Next

The simplest way to resolve your problem is to debug programme using F8 key.