我有一个sub1，它包含一个带有 AdressFound 变量的FIND函数，并且有一个Do Loop会执行另一个sub2。当工作流返回到sub1时，会执行 FindNext 命令。第一轮循环执行正常，但在下一轮循环时，出现“对象变量或With块变量未设置”的错误。

我有一个变量 AddressFound，并且工作簿对象被声明为Public。

变量 AddressFound 在sub2中未被使用。

我找不出为什么变量 AddressFound 会引发这个错误。

完整的VBA代码

Option Explicit

Public AddressFound As Range
Public RowToCopy As Long
Public MidPointsWorkBook As Workbook

    Sub BonusTemplateGroup()
    '
    ' BonusTemplateGroup Macro
        Dim BonusTemplateCode As String
        Dim BonusTemplateCodeCount As Long
        Dim BonusTemplateCodeRange As Range
        Dim CodeRow As Long
        Dim LastRow As Long
        Dim FirstAddress As String
        Dim EndAddress As Range
        Dim AddressFound As Range

        Set MidPointsWorkBook = Workbooks("Midpoints_Work_File_Rev3Test.xlsm")
        ActiveWorkbook.Sheets("Salary").Activate
        Set BonusTemplateCodeRange = ThisWorkbook.Sheets("Salary").Columns("J")
        BonusTemplateCode = Range("SelectedTemplate").Value

        BonusTemplateCodeCount = Application.WorksheetFunction.CountIf(BonusTemplateCodeRange, Range("SelectedTemplate").Value)

        With BonusTemplateCodeRange

            Set AddressFound = .Find(BonusTemplateCode, LookIn:=xlValues)
            MsgBox "addressfound: " & AddressFound.Address

            If Not AddressFound Is Nothing Then
            FirstAddress = AddressFound.Address

              Do
                    ActiveWorkbook.Sheets("Salary").Range(AddressFound.Address).Activate
                    RowToCopy = ActiveCell.Row

                        Call Employee_Bonus

                    Set AddressFound = .FindNext(AddressFound)
                Loop Until AddressFound.Address = FirstAddress       at this line, the error happens after the 1st loop
    VBA Tag AddressFound.Address = <Object variable or With block variable not set
            End If
        End With

    End Sub

编辑： 在收到一些建议后，我回去分析sub2是否会在AddressFound变量未被使用的情况下引发错误。通过删除sub2命令的部分段落，我发现了一个会引发错误的部分

filePath = BonusTemplateName 
'Copy data from Midpoint file to Bonus Template 
Workbooks.Open Filename:=filePath, UpdateLinks:=0 
'MsgBox filePath 
Range("$B$3").Value = EmployeeName 
Range("$B$4").Value = EmployeeJobTitle 
Range("$C$5").Value = AnnualSalary 
Range("$B$6").Value = Bonus 
Range("$C$9").Value = Median 
'Find Bonus Template row number for "OBJECTIVE" word 
Set OBJECTIVE = Range("A:A").Find("OBJECTIVE", LookAt:=xlWhole)

这段命令段会打开另一张Excel工作表，并在其中读写数据。为什么这条命令会破坏FindNext函数？

解决方案

基于上面的评论：请看下面的代码，了解如何避免你所遇到的问题。

此外...

1. 你不需要（也不应该）使用Globals来保存被调用方法所需的信息——只需直接将信息作为参数传递给被调用的方法（Sub/Function）。
2. 在进行Excel的 VBA编程时，最好避免激活/选择工作表或范围。几乎从不需要，这会让代码变得更脆弱、调试起来也更困难。值得回顾：如何在Excel VBA中避免使用Select？
3. 刚刚注意到你把 AddressFound 同时声明为全局变量和在 BonusTemplateGroup 里面的变量——如果你这么做，那么 BonusTemplateGroup 里的代码将使用局部变量，而全局变量将保持为 Nothing。

Sub BonusTemplateGroup()

    Dim BonusTemplateCode As String
    Dim BonusTemplateCodeRange As Range
    Dim FirstAddress As String
    Dim AddressFound As Range

    Set BonusTemplateCodeRange = ThisWorkbook.Sheets("Salary").Columns("J")
    BonusTemplateCode = Range("SelectedTemplate").Value 'where/how is this range defined?

    With BonusTemplateCodeRange
        'Use `xlWhole` unless you want partial matches, and in any case you should
        '  specify something for that parameter
        Set AddressFound = .Find(BonusTemplateCode, LookIn:=xlValues, lookat:=xlWhole)
        If Not AddressFound Is Nothing Then
            FirstAddress = AddressFound.Address
            Do
                EmployeeBonus AddressFound 'pass the found cell to your sub
                Set AddressFound = .FindNext(AddressFound)
                'need this next line if `EmployeeBonus` might change the content of `AddressFound`
                If AddressFound Is Nothing Then Exit Do
            Loop Until AddressFound.Address = FirstAddress
        End If
    End With

End Sub

'Your sub to handle each matched cell in Col J
Sub EmployeeBonus(c As Range)
    Debug.Print "Doing something with " & c.Address & _
                " on worksheet '" & c.Worksheet.Name & "'" & _
                " in workbook '" & c.Worksheet.Parent.Name & "'"
    c.Value = "x" & c.Value 'cell will no longer match in your `Find`
End Sub

在你的注释之后——似乎你试图嵌套对 Find 的调用，而在使用 FindNext 时不能这样做。相较于下面的写法：

Dim OBJECTIVE as Range
'Find Bonus Template row number for "OBJECTIVE" word 
Set OBJECTIVE = Range("A:A").Find("OBJECTIVE", LookAt:=xlWhole)

...你也可以改用 Match，它不会产生嵌套的 Find 问题：

Dim OBJECTIVE As Variant '<<<
'Find Bonus Template row number for "OBJECTIVE" word 
OBJECTIVE = Application.Match("OBJECTIVE", Range("A:A"), 0)
If Not IsError(OBJECTIVE) Then
    ' "OBJECTIVE" was found on row# `OBJECTIVE`
Else
    'search term not found
End if