Performing Vlookup in VBA from different workbook

Question

Hello Friends & Community,

Seeking Help again, I am trying to do a vlookup VBA, the reference data is in another workbook. I was thinking to assign variable for the data workbook & worksheet, but wasnt able to make it work. The sub can run successfully, but do not return any result in the desired worksheet. Please help!

Explanations:
WbVlookup is the variable for vlookup data workbook
BuildplanRange is the varibale for vlookup data range
The result need to run from Row 14 to the last result row

Thoughts of mine: It seems to me the variable wasnt assigned correctly for both workbook and datarange, but i wasn't able to figure out what was wrong with the syntax, so hoping somebody can help

Sub VlookupForColumnGHIJ()

Dim EndofRow As Long
Dim RowX As Integer
Dim WbVlookup As Workbook
Dim BuildplanRange As Range



Set WbVlookup = Workbooks.Open("C:\Users\DengSilin\Downloads\Copy of 98289 (FL512S)_Allocation File_WW18 (002)_Cindy_02022021.xlsx")
Set BuilPlanRange = WbVlookup.Worksheets("Build Plan").Column("F:I")


EndofRow = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
For RowX = 14 To EndofRow
Cells(RowX, 6) = Application.WorksheetFunction.VLookup(Cells(RowX, 3), BuilPlanRange, 2, 0)

Next RowX

End Sub

PeterT PeterT · Accepted Answer · 2021-02-10T03:22:22

Your code is making an assumption about which workbook is Active. My suggestion is to create a habit to ALWAYS fully reference your workbooks and worksheets, it saves lots of time chasing errors.

So your code starts with

Dim WbVlookup As Workbook
Dim BuildplanRange As Range
Set WbVlookup = Workbooks.Open("C:\Users\DengSilin\Downloads\Copy of 98289 (FL512S)_Allocation File_WW18 (002)_Cindy_02022021.xlsx")
Set BuilPlanRange = WbVlookup.Worksheets("Build Plan").Column("F:I")

which is very explicit and clear. But next, you are referencing Cells:

EndofRow = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

The Cells in this case reference the current ActiveWorkbook by default. Because you've just opened the WbVlookup workbook, this is very likely your active workbook. I'm guessing this is not what you want.

So a quick change to this may work

Dim wb As Workbook
Dim ws As Worksheet
Set wb = Thisworkbook
Set ws = wb.Sheets("IwantThisSheet")

Dim EndofRow As Long
With ws
    EndofRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    For RowX = 14 To EndofRow
        .Cells(RowX, 6) = Application.WorksheetFunction.VLookup(.Cells(RowX, 3), _
                                                             BuilPlanRange, 2, 0)
    Next RowX
End With

Performing Vlookup in VBA from different workbook

1 Answers