VBA跨工作簿复制数据时首个If语句报下标越界错误

核心报错原因 #

下标越界错误出现在第一个If判断行，本质是代码引用的工作簿或工作表对象不在当前可访问的Workbooks/Sheets集合中。注意：VBA编辑器中「工具->引用」功能是用于加载类型库、COM组件的，不能用来绑定已打开的Excel工作簿，这是跨工作簿编码最常见的认知误区。

常见触发场景排查 #

• 文件名/工作表名不匹配：代码中写的Report.xlsm、Big screen.xlsm、CopyDatabase、CELL_1、SHEET1必须和窗口标题栏的文件名、工作表标签名完全一致，注意排查是否存在隐藏的文件扩展名、多余空格、重复打开生成的(1)类后缀、全角字符等差异。
• 工作簿不在同一Excel进程：如果双击文件分别打开两个工作簿，可能触发多Excel进程隔离，跨进程的工作簿无法通过Workbooks()集合直接访问。解决方法是关闭所有Excel窗口，先打开Excel主程序，再通过「文件->打开」菜单依次打开两个目标文件即可。
• 无限定符的对象默认指向当前激活表：原代码中Rows.Count没有指定所属工作表，会默认读取当前激活表的最大行号，如果激活表不是预期的CopyDatabase表，会导致行号读取错误，后续循环访问单元格时也可能触发越界。
• 不规范写法隐患：原代码中Cells()(1,2)属于冗余的错误写法，直接写Cells(1,2)即可，虽然不会直接触发报错，但会增加对象引用的不确定性。

修正后可运行代码 #

' 模块顶部强制变量声明，避免未定义变量引发的逻辑错误
Option Explicit

Sub update_cell1_InProcess()
    Dim lRow As Long, i As Long, b As Long
    Dim wbReport As Workbook, wbBigScreen As Workbook
    Dim wsCopySource As Worksheet, wsMatch As Worksheet, wsPasteTarget As Worksheet
    
    ' 提前绑定所有对象，简化后续写法，也方便快速定位不存在的对象
    Set wbReport = Workbooks("Report.xlsm")
    Set wbBigScreen = Workbooks("Big screen.xlsm")
    Set wsCopySource = wbReport.Sheets("CopyDatabase")
    Set wsMatch = wbBigScreen.Sheets("CELL_1")
    Set wsPasteTarget = wbBigScreen.Sheets("SHEET1")
    
    b = 31
    ' 明确指定Rows所属工作表，避免取错最大行号
    lRow = wsCopySource.Cells(wsCopySource.Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To lRow
        If wsCopySource.Cells(i, 5).Value = wsMatch.Cells(1, 2).Value Then
            If wsCopySource.Cells(i, 1).Value = wsMatch.Cells(1, 1).Value Then
                If wsCopySource.Cells(i, 6).Value <> "" Then
                    If wsCopySource.Cells(i, 8).Value = "" Then
                        ' 去掉Activate/Select操作，直接指定粘贴目标，运行更稳定效率更高
                        wsCopySource.Rows(i).Copy Destination:=wsPasteTarget.Cells(b + 1, 1)
                        b = b + 1
                    End If
                End If
            End If
        End If
    Next
    
    Application.CutCopyMode = False
End Sub

快速定位技巧 #

调试时在第一个If语句前打断点（点击代码行左侧灰色边栏即可），代码运行到断点处暂停后，按Ctrl+G调出立即窗口，逐行输入以下命令回车测试，哪行返回报错就说明对应对象不存在，直接修正名称即可：

?Workbooks("Report.xlsm").Name
?Workbooks("Big screen.xlsm").Name
?Workbooks("Report.xlsm").Sheets("CopyDatabase").Name
?Workbooks("Big screen.xlsm").Sheets("CELL_1").Name

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