vba Vlookup across workbooks

2
votes

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.

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.

I appreciate any help with this vlookup issue. Thanks.

 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)]
2
excelvba
Are book1 and book2 workbook variables? If so, the "Cells" within "Range(...)" should also be qualified by 2workbooks(book2).sheets(5)." etc. – Excel Developers
What's the exact error? – StoriKnow
The exact error when using the above piece of code is shown in cell as #VALUE!. When replacing cells(j,c+2.value with variant variable (test), the value of test is error 2015. – user2000380
Thanks, resolved the issue with all help from all and replaceing [Vlookup] with Application.Vloopup.. – user2000380

2 Answers

3
votes

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
2
votes

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

enter image description here