VBA跨工作簿查找指定文本复制时遇Runtime Error 91求助及优化

错误根源分析 #

你遇到的Runtime Error 91，核心原因是直接在Find方法后链式调用Offset：当Find找不到目标值时会返回Nothing，此时调用Offset(0,1)相当于对空对象执行操作，直接触发报错，你写的If Not r1 Is Nothing Then根本没机会执行。

核心修复步骤 #

1. 拆分Find与Offset操作：先执行Find获取结果，判断结果不为空后，再调用Offset
2. 补全Find方法的关键参数（如SearchOrder、MatchCase），避免Excel默认行为导致的查找异常
3. 移除无意义的Activate调用，直接通过对象引用操作单元格

优化后的完整代码 #

Sub AdjustMasterFile()
    ' 严格变量声明
    Dim wbMain As Workbook, wbMainClean As Workbook
    Dim wsMain As Worksheet, wsMainClean As Worksheet
    Dim rFind As Range, rCopy As Range
    Dim lastRowMain As Long, lastColClean As Long
    Dim srcVal As String
    Dim i As Long, j As Long
    Dim startTime As Double, totalTime As Double
    
    ' 绑定工作簿和工作表（确保文件已打开）
    Set wbMain = Workbooks("MainImportFileVBA.xlsm")
    Set wbMainClean = Workbooks("MainImportFileVBA_Cleaned Output.xlsm")
    Set wsMain = wbMain.Sheets("Sheet1")
    Set wsMainClean = wbMainClean.Sheets("Tabelle1")
    
    ' 获取行列边界（避免重复引用工作表）
    lastRowMain = wsMain.Cells(wsMain.Rows.Count, 1).End(xlUp).Row
    lastColClean = wsMainClean.Cells(1, wsMainClean.Columns.Count).End(xlToLeft).Column
    
    ' 开启效率优化开关
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    startTime = Timer
    
    ' 外层循环：遍历目标工作簿的表头（搜索关键词）
    For i = 1 To lastColClean
        srcVal = wsMainClean.Cells(1, i).Value
        ' 内层循环：遍历源工作簿的每一行
        For j = 2 To lastRowMain
            ' 先执行Find，判断结果是否存在
            Set rFind = wsMain.Range("A" & j & ":BRA" & j).Find( _
                What:=srcVal, _
                LookIn:=xlValues, _
                LookAt:=xlPart, _
                SearchOrder:=xlByColumns, _
                MatchCase:=False)
            
            ' 找到目标后再执行Offset和赋值
            If Not rFind Is Nothing Then
                Set rCopy = wsMainClean.Cells(j, i)
                ' 直接赋值代替Copy，提升效率
                rCopy.Value = rFind.Offset(0, 1).Value
            End If
        Next j
    Next i
    
    ' 恢复Excel默认设置
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    totalTime = Timer - startTime
    MsgBox "总耗时：" & totalTime & " 秒!"
End Sub

大数据集优化要点 #

• 严格变量声明：建议在模块顶部添加Option Explicit，强制所有变量声明，避免隐式类型转换的性能损耗
• 移除Select/Activate：直接通过工作表、单元格对象引用操作，避免Excel界面交互的额外开销
• 替换Copy为直接赋值：rCopy.Value = rFind.Offset(0,1).Value比Copy方法快数倍，尤其适合大数据量场景
• 关闭Excel后台功能：临时禁用屏幕更新、事件触发、自动计算，大幅减少运行时的资源占用
• 避免重复对象引用：将工作表、行列数提前赋值给变量，减少重复访问Excel对象的次数
• 明确Find参数：指定SearchOrder、MatchCase等参数，避免依赖Excel默认值导致的查找不稳定

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