vba 跨工作簿的vba Vlookup
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15658334/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba Vlookup across workbooks
提问by user2000380
I seem to have an error in the below syntax line. I believe the issue lies with range parameter of workbook book1. I cannot figure out why. Basically I'm tring to vlookup across 2 workbooks.
我似乎在下面的语法行中有错误。我认为问题在于工作簿 book1 的范围参数。我不明白为什么。基本上我想在 2 个工作簿中查找。
The code is invoked from workbook - book1. Just before this line of code workbook - book2 is activated. Both the workbooks are open. I captured the error code 2015 by replacing the left side with a variant variable.
该代码是从工作簿 - book1 调用的。就在这行代码工作簿之前 - book2 被激活。两本工作簿均已打开。我通过用变体变量替换左侧来捕获错误代码 2015。
I appreciate any help with this vlookup issue. Thanks.
感谢您对此 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)]
回答by David Zemens
You've provided only a snippet of code, but first things first let's make sure you have all the variables defined. I have also added a few more to simplify and possibly help trap errors.
您只提供了一段代码，但首先让我们确保您已定义所有变量。我还添加了一些以简化并可能帮助捕获错误。
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
回答by David Zemens
Below is a simple example.
下面是一个简单的例子。
Sub VlookMultipleWorkbooks()
Dim lookFor As Range
Dim srchRange As Range
Dim book1 As Workbook
Dim book2 As Workbook
Dim book2Name As String
book2Name = "test.xls" 'modify it as per your requirement
Dim book2NamePath As String
book2NamePath = ThisWorkbook.Path & "\" & book2Name
Set book1 = ThisWorkbook
If IsOpen(book2Name) = False Then Workbooks.Open (book2NamePath)
Set book2 = Workbooks(book2Name)
Set lookFor = book1.Sheets(1).Cells(2, 1) ' value to find
Set srchRange = book2.Sheets(1).Range("B:C") 'source
lookFor.Offset(0, 1).Value = Application.VLookup(lookFor, srchRange, 2, False)
End Sub
Function IsOpen(strWkbNm As String) As Boolean
On Error Resume Next
Dim wBook As Workbook
Set wBook = Workbooks(strWkbNm)
If wBook Is Nothing Then 'Not open
IsOpen = False
Set wBook = Nothing
On Error GoTo 0
Else
IsOpen = True
Set wBook = Nothing
On Error GoTo 0
End If
End Function
