CODEWITHSUNDEEP
excelvbavlookup
zaanwar
zaanwar

Reputation: 65

Using VLookup in VBA to reference another Excel file

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

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

Upvotes: 2

Views: 46980

Answers (2)

tells
tells

Reputation: 630

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.

Upvotes: 0

user4039065
user4039065

Reputation:

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.

Upvotes: 5

Related Questions