VBA查找Excel多页表头时无法捕获空Range触发对象未设置错误

问题场景 #

• 需要扫描Excel工作表内所有值为Product Name的表头单元格，适配多分页、多表头的表结构，分页数量不固定。
• 原有Find+FindNext循环逻辑在找不到更多匹配项时，无法识别空Range对象，反复触发运行时错误91：对象变量或With块变量未设置，尝试IsEmpty、IsNull等判断写法均未生效。

错误根因 #

• VBA中Range属于对象类型，判断对象是否未赋值唯一合法写法是Is Nothing判断：IsEmpty仅对Variant类型的普通值变量生效，IsNull仅用于判定Null值，二者都无法识别未初始化的对象变量，这是空判断失效的核心原因。
• 原代码逻辑顺序倒置：先扩容数组、递增下标，再执行FindNext赋值，导致新扩容的数组元素默认是未赋值的Nothing状态，后续访问该元素直接触发错误。
• 原生FindNext方法遍历完所有匹配项后会自动回到第一个匹配结果，不加终止判断会陷入死循环。

修正后可运行代码 #

Sub testingBreak()
    Dim starting As String
    starting = "starting"
    Dim productNameRange() As Range
    Dim firstMatchAddr As String ' 记录首个匹配项地址，避免FindNext死循环
    Dim rowCount As Long ' 用Long避免行数超过Integer上限触发溢出
    rowCount = 0
    Dim r As Long
    
    ReDim productNameRange(rowCount)
    
    ' 执行第一次查找
    Set productNameRange(rowCount) = Sheets(starting).Cells.Find( _
        What:="Product Name", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)

    If productNameRange(rowCount) Is Nothing Then
        MsgBox "Search Error: Header Not found"
        Exit Sub
    End If
    
    ' 保存第一个匹配项的地址，作为循环终止标记
    firstMatchAddr = productNameRange(rowCount).Address
    
    ' 循环查找所有匹配项
    Do
        ' 查找下一个匹配项
        Set productNameRange(rowCount) = Sheets(starting).Cells.FindNext(productNameRange(rowCount))
        ' 未找到新匹配项直接退出循环
        If productNameRange(rowCount) Is Nothing Then Exit Do
        ' 回到首个匹配项说明遍历完成，退出循环
        If productNameRange(rowCount).Address = firstMatchAddr Then Exit Do
        
        ' 确认匹配项有效后再扩容数组、更新计数
        rowCount = rowCount + 1
        ReDim Preserve productNameRange(rowCount)
    Loop
    
    ' 输出结果
    MsgBox (rowCount + 1) & " Row(s) have been found!"
    For r = 0 To rowCount
        MsgBox "表头内容：" & productNameRange(r).Value & vbCrLf & "单元格地址：" & productNameRange(r).Address
    Next r
End Sub

关键修改点 #

• 所有Range对象空值判断统一使用Is Nothing语法，符合VBA对象判定规则。
• 调整循环执行顺序：先执行FindNext查找，第一时间校验返回值有效性，确认有效后再调整数组大小、更新计数，从根源上避免访问未赋值的对象。
• 增加首个匹配项地址校验，解决FindNext回到起点导致的死循环问题。
• 将行计数变量从Integer改为Long，避免工作表行数超过32767时触发溢出错误。
• 移除所有无效的IsEmpty、IsNull判断逻辑，减少冗余代码。

内容的提问来源于stack exchange，提问作者Lopeker