Using VLookup in VBA to reference another Excel file

Question

I'm trying to program a VLookup Table in VBA that references another file. Here is a simple outline of my goal:

Look up value in cell A2 in another Excel file
Pull the information in from column 2 of the other Excel file and place in Cell B2
Move on to cell A3 and repeat the process until there are no more entries left in column A

Here is the code that I already have. I keep getting an error that says "Unable to get the VLookup property of the WOrksheetFunction class." I checked the other posts referencing that error but they were not of any help. Do you all see an error in my code? Or does anyone have a better way of accomplishing this task?

Sub SBEPlannerAdder()

Dim wbk As Workbook
Set wbk = Workbooks.Open("C:\Users\user\Documents\Support File\Planner.xlsx")

With Sheets("Sheet1")

    ' Selects the first cell to check
    Range("A2").Select
    Dim x As Variant
    x = wbk.Worksheets("Sheet1").Range("A1:C1752")

    ' Loops through all rows until an empty row is found
    Do Until IsEmpty(ActiveCell)

        Range(ActiveCell.Offset(0, 1) & ActiveCell.Row).Value = Application.WorksheetFunction.VLookup((ActiveCell.Column & ActiveCell.Row), x, 2, 0)
        ActiveCell.Offset(1, 0).Select
    Loop
End With

Call wbk.Close(False)
End Sub

It is probably because the vlookup is not returning a value or in other words the result is "N/A". If this is the case, remove the .worksheetFunction and run it, you will need to then have an if statement to deal with the 'N/A'. — Scott Craner
Can't you use the vlookup in the sheet itself. So as a function that references the other file and range? Your search string is Column & Row, which will result in one number as string. Is this intentional? — Bas Verlaat

Unknown Unknown · Accepted Answer · 2015-09-23T16:08:42

When you open a workbook, it becomes the active workbook. It seems you were never passing control back to the target workbook.

Sub SBEPlannerAdder()
    Dim rw As Long, x As Range
    Dim extwbk As Workbook, twb As Workbook

    Set twb = ThisWorkbook
    Set extwbk = Workbooks.Open("C:\Users\user\Documents\Support File\Planner.xlsx")
    Set x = extwbk.Worksheets("Sheet1").Range("A1:C1752")

    With twb.Sheets("Sheet1")

        For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            .Cells(rw, 2) = Application.VLookup(.Cells(rw, 1).Value2, x, 2, False)
        Next rw

    End With

    extwbk.Close savechanges:=False
End Sub

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Using VLookup in VBA to reference another Excel file

2 Answers