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
– Scott Craner, Commented Sep 23, 2015 at 15:57
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
– Bas Verlaat, Commented Sep 23, 2015 at 16:04

Community · Accepted Answer · 2017-05-23 11:44:26Z

5

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.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Sep 23, 2015 at 16:08

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

++ Select and Activate are evil!

tells · Accepted Answer · 2015-09-23 16:25:02Z

0

It depends on whether you plan to do this as a one off or repeatedly. I'm assuming repeatedly since doing this manually is not all that difficult.

The first thing I would look at is your arguments. The first two should be ranges. So to be clear, perhaps you could do something like

Dim x As Range
set x = wbk.Worksheets("Sheet1").Range("A1:C1752")
...

Range(ActiveCell.Offset(0, 1) & ActiveCell.Row).Value = Application.WorksheetFunction.VLookup(Range(Activecell.Address), x, 2, 0)

The important bits are making sure your first two arguments are Ranges for the Vlookup function.

answered Sep 23, 2015 at 16:25

tells

6304 silver badges11 bronze badges

Collectives™ on Stack Overflow

Using VLookup in VBA to reference another Excel file

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related