Looping Vlookup Through a Variable Range

Philip1957

Board Regular
Joined
Sep 30, 2014
Messages
149
Greetings,

I'm trying to set up a macro that is beyond my current skill level with VBA and I'm struggling.

I have two workbooks ("IP_Master" and "IP Lookup") and I need to look up information in one to transfer it to the other. The number of rows in the ranges involved in both workbooks can change from week to week.
I have defined the two workbooks as variables and Named the data range in each.

I can get the Vlookup to run once with this:

VBA Code:
Private Sub IP_Vlook_Master_to_Lookup()

'Lookup the IP address in IP Master, copy to IP Lookup

Dim wb1 As Workbook
    Set wb1 = ThisWorkbook
Dim wb3 As Workbook
    Set wb3 = Workbooks("IP Lookup.xlsx")
Dim wb4 As Workbook
    Set wb4 = Workbooks("IP_Master.xlsx")
     
wb3.Activate
     
    Range("B2").Select
    ActiveCell = "=VLOOKUP(A2,IP_Master.xlsx!IPM_tbl,2,0)"
End Sub

I'm having a heck of a time getting it to loop through the entire range though. Here's the most recent of multiple failed attempts.

VBA Code:
Private Sub IP_Vlook_Master_to_Lookup()

'Lookup the IP address in IP Master, copy to IP Lookup

Dim wb1 As Workbook
    Set wb1 = ThisWorkbook
Dim wb3 As Workbook
    Set wb3 = Workbooks("IP Lookup.xlsx")
Dim wb4 As Workbook
    Set wb4 = Workbooks("IP_Master.xlsx")
Dim i As Integer
Dim lrow As Integer
   lrow = Workbooks("IP Lookup.xlsx!IPL_tbl")(Rows.Count, "A").End(xlUp).Row
   
wb3.Activate
     
For i = 2 To lrow
   
    Range("B2").Select
    'ActiveCell.Value = Application.WorksheetFunction.VLookup(Range("B2" & i), 2, 0)
    
Next i

End Sub

This hangs at the"lrow=" statement. I'm hoping someone can educate me on what I'm doing wrong here.

I also don't understand why this works:

VBA Code:
ActiveCell = "=VLOOKUP(A2,IP_Master.xlsx!IPM_tbl,2,0)"

But this doesn't, even though I've defined the variable wb4 as the workbook "IP_Master"

VBA Code:
ActiveCell = "=VLOOKUP(A2,wb4!IPM_tbl,2,0)"

Thanks in advance for any assistance.
I know that time is valuable and appreciate you spending some on my problem.

~ Phil
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,984
How about something like

VBA Code:
With Range("B:B")
    With Range(.Cells(2, 1), .Cells(lrow, 1))
        .FormulaR1C1 = "=VLOOKUP(RC[-1], IP_Master.xlsx!IPM_tbl,2,0)"
    End With
End With
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
773
For lrow:
VBA Code:
    With Workbooks("IP Lookup.xlsx").Worksheets("IPL_tbl")
        lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With


ActiveCell = "=VLOOKUP(A2,wb4!IPM_tbl,2,0)" does not work because wb4 is an object and you are passing it to the cell as literal string. You need to do something like this:

VBA Code:
     ActiveCell.Formula = "=VLOOKUP(A2,'" & wb4.Name & "'!IPM_tbl,2,0)"

(not tested)
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,984
If you want the workbook address in the formula

VBA Code:
ActiveCell.Formula = "=VLOOKUP(A2," & wb4.Names(""IPM_tb1").RefersToRange.Address(,,,True) & ",2,0)"
 

Philip1957

Board Regular
Joined
Sep 30, 2014
Messages
149

ADVERTISEMENT

mikerickson / rlv01,

Thank you both for your responses. I will try out your suggested solutions at the first opportunity, which will probobly not be until later in the week.

My priorities don't always align with my manager's . . . :rolleyes:
 

Philip1957

Board Regular
Joined
Sep 30, 2014
Messages
149
Gentlemen,
Success!
I had to make a change to the setting of lrow. Using the name of the range (IPL_tbl) gave me a subscript out of range error. I changed it to Sheet 1 and it works fine.
Here's what I ended up with.

VBA Code:
Private Sub IP_Vlook_Master_to_Lookup()
'Lookup the IP address in IP Master, copy to IP Lookup      WORKS
Dim wb1 As Workbook
    Set wb1 = ThisWorkbook
Dim wb3 As Workbook
    Set wb3 = Workbooks("IP Lookup.xlsx")
Dim wb4 As Workbook
    Set wb4 = Workbooks("IP_Master.xlsx")
Dim lrow As Integer

    With Workbooks("IP Lookup.xlsx").Worksheets("Sheet1")
        lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With  

    With Range("B:B")
        With Range(.Cells(2, 1), .Cells(lrow, 1))
            .FormulaR1C1 = "=VLOOKUP(RC[-1], IP_Master.xlsx!IPM_tbl,2,0)"
        End With
    End With
End Sub


Thank you very much for the help and education.

~ Phil
 

Watch MrExcel Video

Forum statistics

Threads
1,129,763
Messages
5,638,215
Members
417,014
Latest member
dualwieldbacon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top