excel - 使用On Error GoTo逻辑时出现运行时错误91

Question

我目前遇到一个问题，我在excel VBA中有一些逻辑，可以让我跳过部分函数。问题是，它在某些地区有效，但在其他地区无效。我有3个可跳过区域，第一个有两个工作，但最后一个似乎没有。我目前遇到运行时错误91:未设置对象变量或With块变量，但是我很清楚在代码部分开始之前设置了变量。

我曾尝试对变量进行硬编码，并使用单独的变量，但不幸的是，对于最后一部分似乎没有任何效果。

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

我的预期结果是，如果需要，每个部分都将启动，并且复制行将被正确复制。当代码正常运行时(IE:find函数正在寻找的值实际上存在)，它可以正常工作，但是当值不存在时，它对我毫无用处。

您能提供的任何帮助或建议将不胜感激。提前致谢!

Answer 1

不需要Let。

指定Find的可选参数(LookIn，LookAt等)。从文档:

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.

不要以为Find成功。这包括避免在.Row之后最后链接.Column或Find或其他调用，例如...Cells.Find("Enterprise").Row。通常的做法是检查查找结果是否不是空:

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