VBA跨工作簿使用VLookup函数报运行时错误1004如何解决

你代码触发运行时错误1004的根源是跨工作簿Range对象引用没有显式绑定上下文：

• Workbooks.Open方法执行后不会自动把打开的文件设为活动工作簿，你后续直接用Workbooks("test.xlsx").Sheets("Sheet1").Range(...)调用时，如果工作簿加载未完成、或者文件名索引匹配异常，VBA会尝试在全局上下文下解析Range对象，直接触发调用失败。
• 额外隐患：
  • 存储VLookup返回值的变量声明为String类型，若返回值为数值、空值、错误值时会触发类型转换错误。
  • 未做VLookup匹配失败的错误处理，只要查找值不存在就会直接中断代码。

Sub CrossWbVLookup()
    Dim srcWb As Workbook
    Dim valFromSht1 As Variant, valFromSht2 As Variant
    Dim searchKey As String
    Dim rngSht1 As Range, rngSht2 As Range
    
    ' 配置查找参数
    searchKey = "202217"
    
    ' 显式绑定打开的源工作簿，从根源避免对象引用错误
    Set srcWb = Workbooks.Open(Filename:="X:\test.xlsx", ReadOnly:=True)
    ' 提前定义两个工作表的查找范围，按需调整范围地址和工作表名
    Set rngSht1 = srcWb.Sheets("Sheet1").Range("A1:C999")
    Set rngSht2 = srcWb.Sheets("Sheet2").Range("A1:C999")
    
    ' 捕获VLookup无匹配的错误，避免代码中断
    On Error Resume Next
    valFromSht1 = Application.WorksheetFunction.VLookup(searchKey, rngSht1, 2, 0)
    valFromSht2 = Application.WorksheetFunction.VLookup(searchKey, rngSht2, 3, 0) ' 按需调整返回列号
    On Error GoTo 0
    
    ' 匹配结果判断（可按需修改后续逻辑）
    If IsError(valFromSht1) Then Debug.Print "Sheet1无匹配结果"
    If IsError(valFromSht2) Then Debug.Print "Sheet2无匹配结果"
    
    ' 操作完成关闭源工作簿，避免文件占用
    srcWb.Close SaveChanges:=False
End Sub

• 打开工作簿时直接将对象赋值给Workbook类型变量，后续所有对该工作簿的操作都基于这个显式绑定的对象，不需要依赖文件名索引、活动窗口状态，彻底解决Range调用的1004错误。
• 提前将查找范围定义为Range对象，避免跨上下文解析Range的异常。
• 返回值变量改为Variant类型，兼容所有可能的返回结果类型，不会触发隐式转换错误。
• 增加错误捕获逻辑，VLookup匹配失败时代码不会直接中断，可自定义无匹配时的处理逻辑。
• 操作完成后显式关闭源工作簿，避免文件在后台驻留占用。

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