VBA跨工作簿VLookup触发1004错误求助及代码优化建议

你遇到的1004错误主要来自几个典型的VBA新手陷阱，我帮你拆解并修复：

错误原因分析 #

• 拼写笔误：代码里的x1Down、x1ToRight是错误写法，应该是xlDown和xlToRight（注意是字母L，不是数字1），这会直接导致Range操作失败。
• 跨表引用不明确：跨工作簿引用Range(Cells(2,1), Cells(Lastzip,31))时，Cells默认指向当前激活的工作表，而非目标工作簿的工作表，这会造成引用范围混乱。
• 数据类型不匹配：用Integer类型的Val接收VLookup结果，但日期本质是数值（可能超出Integer范围），且匹配失败时会返回错误值，用Variant类型更安全。
• 过度依赖Activate/Select：频繁激活工作表、选择单元格会让代码不稳定，且运行效率低下。

修复后的代码 #

Sub Add_Dates()
    Application.CutCopyMode = False
    Application.ScreenUpdating = False ' 关闭屏幕更新提升运行速度
    
    ' 用变量绑定工作簿和工作表，避免名称引用错误
    Dim wbCombine As Workbook, wbZipe As Workbook
    Dim wsCombineZSLB As Worksheet, wsZipeZSLB As Worksheet
    Dim LastRow As Long, Lastzip As Long
    Dim targetCol As Long
    Dim lookupResult As Variant ' 用Variant接收可能的错误值或日期
    
    ' 打开并绑定目标工作簿
    Set wbCombine = Workbooks.Open("C:\(%)\combine zslb and zpdi.xlsm")
    Set wsCombineZSLB = wbCombine.Sheets("zslb")
    
    ' 打开并绑定源数据工作簿
    Set wbZipe = Workbooks.Open("C:\(%)\zipe zpdi zslb.xlsx")
    Set wsZipeZSLB = wbZipe.Sheets("ZSLB")
    
    ' 可靠获取最后一行（避免中间空行导致的错误）
    LastRow = wsCombineZSLB.Cells(wsCombineZSLB.Rows.Count, 1).End(xlUp).Row
    Lastzip = wsZipeZSLB.Cells(wsZipeZSLB.Rows.Count, 1).End(xlUp).Row
    
    ' 获取要写入的目标列（A列右侧第一个空列）
    targetCol = wsCombineZSLB.Cells(2, wsCombineZSLB.Columns.Count).End(xlToLeft).Column + 1
    
    ' 循环执行VLookup
    Dim i As Long
    For i = 2 To LastRow
        ' 明确指定Range的归属工作表，避免引用混乱
        lookupResult = Application.VLookup(wsCombineZSLB.Cells(i, 1).Value, _
                                          wsZipeZSLB.Range(wsZipeZSLB.Cells(2, 1), wsZipeZSLB.Cells(Lastzip, 31)), _
                                          31, False)
        
        ' 处理匹配失败的情况，避免写入错误值
        If Not IsError(lookupResult) Then
            wsCombineZSLB.Cells(i, targetCol).Value = lookupResult
        Else
            wsCombineZSLB.Cells(i, targetCol).Value = "无匹配" ' 也可以改为留空：= ""
        End If
    Next i
    
    Application.ScreenUpdating = True ' 恢复屏幕更新
    MsgBox "日期匹配完成！", vbInformation
End Sub

进一步优化建议 #

• 避免硬编码路径：如果两个工作簿在同一文件夹，可以用ThisWorkbook.Path获取当前工作簿路径，比如wbCombine = Workbooks.Open(ThisWorkbook.Path & "\combine zslb and zpdi.xlsm")，提升代码兼容性。
• 添加错误捕获：可以在代码开头加入On Error Resume Next或On Error GoTo ErrorHandler，处理工作簿打开失败等意外情况。
• 批量写入数据：如果数据量很大，可以把VLookup结果先存入数组，再一次性写入工作表，比逐行写入效率高很多。
• 用Long代替Integer：Excel的行号可能超过Integer的最大值（32767），用Long可以避免溢出错误。

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