excel - vba 跨工作簿的 Vlookup

Question

下面的语法行似乎有错误。我认为问题在于工作簿 book1 的范围参数。我不明白为什么。基本上我尝试在 2 个工作簿中进行 vlookup。

该代码是从工作簿 - book1 调用的。就在这行代码之前，工作簿 - book2 被激活。两本工作簿均已打开。我通过用变体变量替换左侧来捕获错误代码 2015。

非常感谢您对这个 vlookup 问题的帮助。谢谢。

 Cells(j, c + 2).value = [VLookup(workbooks(book2).sheets(5).range(Cells(j, c + 1)), workbooks(book1).sheets(4).range(cells(row1+2,1),cells(row2,col1)), 3, false)]

Answer 1

您只提供了一段代码，但首先我们要确保您已定义所有变量。我还添加了更多内容来简化并可能有助于捕获错误。

Sub VlookMultipleWorkbooks()
Dim lookFor as String
Dim srchRange as Range
Dim book1 as Workbook
Dim book2 as Workbook

'Set some Workbook variables:
Set book1 = Workbooks("Book 1 Name") '<edit as needed
Set book2 = Workbooks("Book 2 Name") '<edit as needed

'Set a string variable that we will search for:
lookFor = book2.sheets(5).range(Cells(j, c + 1))

'Define the range to be searched in Book1.Sheets(4):
Set srchRange = book1.Sheets(4).Range(cells(row1+2,1).Address, cells(row2,col1).Address)

'This assumes that the Book2 is Open and you are on the desired active worksheet:
ActiveSheet.Cells(j, c + 2).value = _
         Application.WorksheetFunction.VLookup(lookFor, _
         book1.Sheets(4).Range(srchRange.Address), 3, False)

End Sub