Excel VBA中Find函数跨工作表查找值运行报错问题求助

核心报错原因 #

• 语法错误：MsgBox (Cells(start_rng).Address)写法错误，start_rng本身就是Range对象，直接调用Address属性即可，嵌套Cells()方法会将start_rng的值作为行列索引传入，触发参数错误。
• 无空值校验：Find方法未匹配到目标值时会返回Nothing，未做判断直接访问Address属性会引发运行时错误。
• Find参数缺失：Find会默认沿用Excel上一次手动查找的配置（如全单元格匹配、格式匹配等），未显式指定关键参数时，极易出现值存在但查找失败的问题。
• 变量声明不规范：Dim rng1, result_rng1 As Range这类写法仅末尾变量为指定类型，前面的rng1默认是Variant类型，lat_row、d1、i等变量未声明，容易引发隐式类型错误。
• 逻辑冗余：无需两次循环处理，第一次调用XLookup时即可直接获取匹配单元格地址，不需要二次调用Find，既提升效率也避免二次查找误差。

优化后可运行代码 #

Option Explicit ' 强制变量声明，避免隐式错误
Private Sub Worksheet_Activate()
    Dim rng1 As Range, result_rng1 As Range
    Dim ws As Worksheet
    Dim nowsheet As Worksheet
    Dim start_rng As Range, end_rng As Range
    Dim lat_row As Long, last_row As Long, i As Long
    Dim d1 As Double
    
    Set nowsheet = Me ' 代码写在当前工作表模块，Me直接指代当前表，避免ActiveSheet歧义
    lat_row = nowsheet.Cells(nowsheet.Rows.Count, 1).End(xlUp).Row
    MsgBox lat_row
    
    For i = 2 To lat_row
        ' 获取要查询的目标工作表
        Set ws = Sheets(nowsheet.Cells(i, 2).Value & "_Orcaflex_Depth")
        last_row = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
        Set rng1 = ws.Range(ws.Cells(2, 2), ws.Cells(last_row, 2))
        
        ' 执行XLookup并直接获取匹配单元格，省略第二次Find操作
        Set result_rng1 = Application.WorksheetFunction.XLookup(nowsheet.Cells(i, 4).Value, rng1, rng1, , -1)
        d1 = result_rng1.Value
        nowsheet.Cells(i, 16).Value = d1
        ' 直接获取地址，无需二次查找
        MsgBox "匹配值地址：" & result_rng1.Address
    Next i
End Sub

如果需要保留原有两次循环的逻辑，仅修复Find部分的错误，可使用以下修改后的代码片段：

' 第二次循环的Find部分修改为：
Set start_rng = results_rng1.Find( _
    What:=nowsheet.Cells(i, 16).Value, _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False _
)
If Not start_rng Is Nothing Then
    MsgBox start_rng.Address
Else
    MsgBox "第" & i & "行未找到匹配值：" & nowsheet.Cells(i, 16).Value
End If

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