Run Time Error 91 When Using On Error GoTo logic

Question

I'm currently running into an issue where I have some logic in excel VBA that allows me to skip parts of a function. The issue is, it works in some areas, but does not work in other areas. I have 3 skippable areas, and the 1st two work, but the last does not appear to. I am currently getting a Run-Time Error 91: Object Variable or With Block variable not set, but I very clearly set the variable before the code section begins.

I've tried hard-coding the variable, and using separate variables, but unfortunately, nothing seems to work for this last piece.

Sub Tester()
'FIRST PART - BUILD HIGH LEVEL METRICS
    Application.DisplayAlerts = False
    Dim tSheet, sARR As Worksheet
    Dim dateSel, rFinder, rFinderTemp As Variant
    Dim pTable As PivotTable
    Dim pRange As Range
    Dim lastRow, lastCol, i, j, rnum As Long
    Dim pAddy As String

    dateSel = "11/17/2019"

    Application.DisplayAlerts = False
    Sheets.Add before:=ActiveSheet
    ActiveSheet.Name = "TempTable"
    Set tSheet = Worksheets("TempTable")
    Set sARR = Worksheets("All_Risk_Report")

    lastRow = sARR.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = sARR.Cells(1, Columns.Count).End(xlToLeft).Column

    Set pRange = sARR.Cells(1, 1).Resize(lastRow, lastCol)
    pAddy = sARR.Name & "!" & pRange.address

    Set pTable = ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=pAddy).CreatePivotTable( _
                TableDestination:=tSheet.Cells(2, 2))

    With pTable
        .PivotFields("GROUPDATE").Orientation = xlPageField
        .PivotFields("GROUPDATE").CurrentPage = dateSel
        .PivotFields("CRL").Orientation = xlColumnField
        .PivotFields("Org_Category").Orientation = xlRowField
        .PivotFields("Change_Request").Orientation = xlDataField
    End With

    With pTable.PivotFields("Sum of Change_Request")
        .Function = xlCount
    End With

    Sheets("TempTable").Activate
    ActiveSheet.Cells(1, 1).Select

    rFinder = 0
    On Error GoTo Skipper1

       Let rFinder = Sheets("TempTable").Cells.Find("Enterprise").Row

        If rFinder > 0 Then
            Sheets("TempTable").Range("C" & rFinder & ":H" & rFinder).Select
            Selection.Copy
            Sheets("Calendar").Activate
            ActiveSheet.Range("K4:P4").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            rFinderTemp = rFinder
        End If

Skipper1:

    Sheets("TempTable").Activate
    ActiveSheet.Cells(1, 1).Select
    rFinder = 0

    On Error GoTo Skipper2
        Let rFinder = Sheets("TempTable").Cells.Find("Home Office").Row

        If rFinder > 0 Then
            Sheets("TempTable").Range("C" & rFinder & ":H" & rFinder).Select
            Selection.Copy
            Sheets("Calendar").Activate
            ActiveSheet.Range("K5:P5").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            rFinderTemp = rFinder
        End If

Skipper2:

    Sheets("TempTable").Activate
    ActiveSheet.Cells(1, 1).Select
    rFinder = 0

    On Error GoTo Skipper3
        Let rFinder = Sheets("TempTable").Cells.Find("WIMT").Row

        If rFinder > 0 Then
            Sheets("TempTable").Range("C" & rFinder & ":H" & rFinder).Select
            Selection.Copy
            Sheets("Calendar").Activate
            ActiveSheet.Range("K6:P6").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            rFinderTemp = rFinder
        End If

Skipper3:

    Sheets("TempTable").Activate
    ActiveSheet.Cells(1, 1).Select

    rnum = rFinder + 1

    If rFinderTemp > rFinder Then
        rnum = rFinderTemp + 1
    End If

        Sheets("TempTable").Range("C" & rnum & ":H" & rnum).Select
        Selection.Copy
        Sheets("Calendar").Activate
        ActiveSheet.Range("K7:P7").Select
        Selection.PasteSpecial Paste:=xlPasteValues

    With Sheets("Calendar")
        For i = 4 To 7
            For j = 11 To 16
                Cells(i, j).Select

                If Selection.Value = "" Then
                    Selection.Value = 0
                End If
            Next
        Next
    End With

    Sheets("TempTable").Activate
    ActiveSheet.Delete
    Sheets("Calendar").Activate

End Sub

My expected result is that each section will kick off, if it is needed, and that the copying rows will be copied over correctly. When the code runs properly (IE: The value the find function is looking for is actually there) it works just fine, but when the value isn't there, it craps out on me.

Any help or advice you could give would be greatly appreciated. Thanks in advance!

You are hiding possible important clues to why your code isn't working through the `On Error GoTo` statements. I would recommend to scrap them. On top of that, there is a lot of `Activate` and `Select` going on which most likely also can just go. — JvdV, Nov 05 '19 at 19:11
What line throws the error? Note, you can drop the `Let` from `Let rFinder = Sheets("TempTable").Cells.Find("Home Office").Row`. — BigBen, Nov 05 '19 at 19:11
The line in question is the line where you read "let rFinder = …" right after the statement "On Error GoTo Skipper3" I'm not sure exactly why this is happening, as the code works for the skipper 2 section, but not this one. — A_Pirates_Favorite_Letter, Nov 05 '19 at 19:12
If will do wonders for your code if you would [avoid `.select` and `.activate` in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Cyril, Nov 05 '19 at 19:13
@Cyril - Thanks for the tip. I'm not the best at Excel VBA, as my expertise is largely in SAS and SQL, so this is definitely a learning experience for me. Ideally, I'd just do all this in SAS, but this project specifically requested an Excel file with a bunch of functionality, so... yay? — A_Pirates_Favorite_Letter, Nov 05 '19 at 19:16
I believe a simple solution to this would be to add `resume next` at the bottom of each `skipper #` so that the code goes back to the existing with or if statements... depends on if you're intending to finish the initial code once errors are resolved. VBA will not keep going with the previous code if an error is present and the error handler doesn't specify to `resume next` — Cyril, Nov 05 '19 at 19:23
Important to keep in mind when using error handling with `goto`... it creates spaghetti code. It may be more appropriate to build in the specific error handling (with if-statement per line in the base subroutine). In the future, this will make it easier to read and follow, not requiring you to scroll left/right/center to find your location/position. — Cyril, Nov 05 '19 at 19:26

BigBen · Accepted Answer · 2019-11-05T19:26:24.263

Let is not needed.
Specify the optional parameters of Find (LookIn, LookAt, etc.). From the docs:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Don't assume that the Find is successful. This includes avoiding chaining a .Row or .Column or other call on the end after Find, e.g. ...Cells.Find("Enterprise").Row. Normal practice is to check if the result of a find is not nothing:

Dim rng as Range
Set rng = Sheets("TempTable").Cells.Find(What:="WIMT", LookIn:=xlValues, LookAt:=xlWhole)

If Not rng Is Nothing Then ' the find is successful
   ... work with rng, with its .Row, etc.
End If

score 0 · Answer 2 · answered Nov 05 '19 at 19:30

Thank you all so much for your updates! I've made some modifications to the code and it works perfectly now. See below:

Sub Tester()
'FIRST PART - BUILD HIGH LEVEL METRICS
    Application.DisplayAlerts = False
    Dim tSheet, sARR As Worksheet
    Dim dateSel, rFinderTemp As Variant
    Dim pTable As PivotTable
    Dim pRange, rFinder As Range
    Dim lastRow, lastCol, i, j, rnum As Long
    Dim pAddy As String

    dateSel = "11/17/2019"

    Application.DisplayAlerts = False
    Sheets.Add before:=ActiveSheet
    ActiveSheet.Name = "TempTable"
    Set tSheet = Worksheets("TempTable")
    Set sARR = Worksheets("All_Risk_Report")

    lastRow = sARR.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = sARR.Cells(1, Columns.Count).End(xlToLeft).Column

    Set pRange = sARR.Cells(1, 1).Resize(lastRow, lastCol)
    pAddy = sARR.Name & "!" & pRange.address

    Set pTable = ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=pAddy).CreatePivotTable( _
                TableDestination:=tSheet.Cells(2, 2))

    With pTable
        .PivotFields("GROUPDATE").Orientation = xlPageField
        .PivotFields("GROUPDATE").CurrentPage = dateSel
        .PivotFields("CRL").Orientation = xlColumnField
        .PivotFields("Org_Category").Orientation = xlRowField
        .PivotFields("Change_Request").Orientation = xlDataField
    End With

    With pTable.PivotFields("Sum of Change_Request")
        .Function = xlCount
    End With

    Sheets("TempTable").Activate

    Set rFinder = Sheets("TempTable").Cells.Find( _
        What:="Enterprise", _
        LookIn:=xlValues, _
        LookAt:=xlWhole)

    If Not rFinder Is Nothing Then
        Sheets("TempTable").Range("C" & rFinder.Row & ":H" & rFinder.Row).Select
        Selection.Copy
        Sheets("Calendar").Activate
        ActiveSheet.Range("K4:P4").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        rFinderTemp = rFinder.Row
    End If

    Sheets("TempTable").Activate


    Set rFinder = Sheets("TempTable").Cells.Find( _
        What:="Home Office", _
        LookIn:=xlValues, _
        LookAt:=xlWhole)

    If Not rFinder Is Nothing Then
        Sheets("TempTable").Range("C" & rFinder & ":H" & rFinder).Select
        Selection.Copy
        Sheets("Calendar").Activate
        ActiveSheet.Range("K5:P5").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        rFinderTemp = rFinder.Row
    End If

    Sheets("TempTable").Activate

    Set rFinder = Sheets("TempTable").Cells.Find( _
        What:="WIMT", _
        LookIn:=xlValues, _
        LookAt:=xlWhole)

    If Not rFinder Is Nothing Then
        Sheets("TempTable").Range("C" & rFinder.Row & ":H" & rFinder.Row).Select
        Selection.Copy
        Sheets("Calendar").Activate
        ActiveSheet.Range("K6:P6").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        rFinderTemp = rFinder.Row
    End If

    Sheets("TempTable").Activate
    ActiveSheet.Cells(1, 1).Select

    If Not rFinder Is Nothing Then
        rnum = rFinder.Row + 1
        Else
        rnum = rFinderTemp + 1
    End If

        Sheets("TempTable").Range("C" & rnum & ":H" & rnum).Select
        Selection.Copy
        Sheets("Calendar").Activate
        ActiveSheet.Range("K7:P7").Select
        Selection.PasteSpecial Paste:=xlPasteValues

    With Sheets("Calendar")
        For i = 4 To 7
            For j = 11 To 16
                Cells(i, j).Select

                If Selection.Value = "" Then
                    Selection.Value = 0
                End If
            Next
        Next
    End With

    Sheets("TempTable").Activate
    ActiveSheet.Delete
    Sheets("Calendar").Activate

End Sub

Run Time Error 91 When Using On Error GoTo logic

2 Answers2