VBA批量处理工作簿触发Object variable or with block variable not set (Error 91) 问题排查请求

我编写了一段批量处理工作簿的VBA脚本，需求是读取F列的工作簿路径，打开后执行复制数据、调整格式、计算公式等操作后另存为新文件。脚本处理前两个包含“Grand Total”和“Opt Total”的工作簿完全正常，但处理第三个和第一个内容完全相同的工作簿时，触发了Object variable or with block variable not set (Error 91)，报错位置是查找“Opt Total”的Activate语句。

错误原因拆解 #

这个Error 91说白了就是：你试图操作一个不存在的对象。具体到你的代码里，当Find方法在B列找不到“Opt Total”时，会返回Nothing（空对象），这时候你直接调用.Activate，自然就会报错。

为什么前两个文件正常，第三个出问题？大概率是这几个情况：

• 虽然你说第三个文件和第一个内容一致，但可能存在隐藏行/列，导致Find扫不到目标文本；
• 或者第三个文件里的“Opt Total”有格式差异（比如多了空格、大小写不对、带隐藏字符）；
• 你的代码太依赖Select和Activate——这些操作很容易因为复制粘贴、窗口切换等操作改变当前活动对象，导致Find的上下文跑偏。

修复方案 #

咱们从根源上解决问题，重构代码时要做到这几点：

1. 彻底抛弃Select/Activate：这些操作不仅慢，还容易引发上下文错误，直接用对象变量引用工作表、单元格；
2. 检查Find的结果：用Set把Find返回的结果存到变量里，先判断变量是不是Nothing，再进行后续操作；
3. 精准处理找不到目标的情况：不要用全局错误跳转来掩盖问题，而是针对性处理每个Find的结果。

重构后的健壮代码 #

Sub Required_Files()
    Dim mainWb As Workbook
    Dim targetWb As Workbook
    Dim targetWs As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim grandTotalCell As Range
    Dim optTotalCell As Range
    Dim saveFilename As String
    Dim i As Long
    
    ' 保存主工作簿对象，避免依赖Activate切换
    Set mainWb = ActiveWorkbook
    
    ' 遍历处理每个文件
    For i = 2 To mainWb.Range("G1").Value
        ' 生成最终保存的文件名
        saveFilename = mainWb.Range("A" & i).Value & " " & _
                       mainWb.Range("D" & i).Value & " " & _
                       mainWb.Range("E" & i).Value & ".xlsx"
        
        ' 打开目标工作簿并引用要操作的工作表（建议改成具体表名，比如"Sheet1"）
        Set targetWb = Workbooks.Open(mainWb.Range("F" & i).Value)
        Set targetWs = targetWb.ActiveSheet
        
        ' 复制整个数据区域并转置粘贴到下方
        lastRow = targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Row
        targetWs.Range("A1", targetWs.Cells(lastRow, targetWs.Columns.Count).End(xlToLeft)).Copy
        targetWs.Cells(lastRow + 3, "A").PasteSpecial Paste:=xlPasteAll, Transpose:=True
        
        ' 删除原数据行和首行
        targetWs.Range("A1", targetWs.Cells(lastRow, "A")).EntireRow.Delete
        targetWs.Rows(1).EntireRow.Delete
        
        ' 获取最后一列并设置单元格值
        lastCol = targetWs.Cells(1, targetWs.Columns.Count).End(xlToLeft).Column
        targetWs.Cells(100, lastCol).Value = 1
        
        ' 查找"Grand Total"，先判断是否找到
        Set grandTotalCell = targetWs.Columns("B").Find(What:="Grand Total", _
            LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False)
            
        If grandTotalCell Is Nothing Then
            MsgBox "工作簿 " & targetWb.Name & " 中未找到Grand Total，跳过处理", vbExclamation
            targetWb.Close SaveChanges:=False
            GoTo NextFileLoop
        End If
        
        ' 查找"Opt Total"
        Set optTotalCell = targetWs.Columns("B").Find(What:="Opt Total", _
            LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False)
            
        ' 设置B100的公式
        If Not optTotalCell Is Nothing Then
            targetWs.Range("B100").Formula = "=IFERROR(" & grandTotalCell.Address(False, False) & _
                                              "-" & optTotalCell.Address(False, False) & ",0)"
        Else
            targetWs.Range("B100").Formula = "=" & grandTotalCell.Address(False, False)
        End If
        
        ' 复制公式到右侧所有列
        targetWs.Range("B100").Copy
        targetWs.Range("B100", targetWs.Cells(100, lastCol)).PasteSpecial Paste:=xlPasteFormulas
        
        ' 设置B100的显示文本
        targetWs.Range("B100").Value = "New Total"
        
        ' 保存并关闭目标工作簿
        targetWb.SaveAs Filename:=saveFilename, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        targetWb.Close SaveChanges:=False
        
NextFileLoop:
        ' 清理对象变量，避免内存泄漏
        Set targetWs = Nothing
        Set targetWb = Nothing
    Next i
    
    MsgBox "Files are ready"
    
    ' 清理主工作簿对象
    Set mainWb = Nothing
End Sub

代码改进点说明 #

• 对象化操作：用mainWb、targetWb、targetWs直接引用工作簿和工作表，彻底避免Select/Activate带来的上下文混乱；
• 安全检查：每次Find后都判断结果是否存在，不会再因为找不到文本触发Error 91；
• 明确上下文：所有单元格操作都加上工作表前缀，确保操作的是正确的目标；
• 容错处理：如果找不到Grand Total，直接提示并跳过该文件，不会影响后续处理；
• 内存管理：每次循环后清理对象变量，避免内存占用。

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