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

Solution

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.

Comments

[Scott Craner]

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'.

[Bas Verlaat]

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?

[Mathieu Guindon]

++ Select and Activate are evil!