Looping Vlookup Through a Variable Range

[Philip1957]

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:

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.

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:

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"

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

 

[mikerickson]

How about something like

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]

For lrow:

    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:

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

(not tested)

 

[mikerickson]

If you want the workbook address in the formula

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

 

[Philip1957]

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

 

[Philip1957]

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.

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

 

[mikerickson]

I'm glad we were able to help.