VLookup VBA Loop

Jones1413

New Member
Joined
Jul 26, 2019
Messages
25
Hello,

I need to add a Vlookup to my VBA code for the following:

The main sheet is called 'Master Sheet' and the table array is in a sheet titled 'Leader Reference'

=VLOOKUP(K2, 'Leader Reference'!A$2:B$607, 2, FALSE)

The return value is going to start in L2 then L3, L4 and so on, on the Master Sheet and continue down. The number of rows vary each week on the report so I don't know what the last row in column L will be with the data.

I need help adding the above VLOOKUP formula to VBA so it will loop down each row of column K until the end of the sheet. If there is no value returned, I want it to say "N/A" in column L.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,644
Office Version
  1. 2010
Platform
  1. Windows
try this code which uses variant arrays and is much faster than using vlookup, but it does the same thing:
VBA Code:
Sub test()
With Worksheets("Leader Reference")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastrow, 2))
End With
With Worksheets("Master Sheet")
lastrow2 = .Cells(Rows.Count, "K").End(xlUp).Row
srch = .Range(.Cells(1, 11), .Cells(lastrow2, 12))
For i = 2 To lastrow2
  srch(i, 2) = "N/A"
  For j = 1 To lastrow
   If srch(i, 1) = inarr(j, 1) Then
    srch(i, 2) = inarr(j, 2)
    Exit For
   End If
  Next j
Next i
.Range(.Cells(1, 11), .Cells(lastrow2, 12)) = srch

End With

End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
You don't need a loop for that.
VBA Code:
With Sheets("Master Sheet")
    .Range("L2:L" & .Range("K" & Rows.Count).End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(K2, 'Leader Reference'!A$2:B$607, 2, FALSE) , ""N/A"")
End With
 

Jones1413

New Member
Joined
Jul 26, 2019
Messages
25
You don't need a loop for that.
VBA Code:
With Sheets("Master Sheet")
    .Range("L2:L" & .Range("K" & Rows.Count).End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(K2, 'Leader Reference'!A$2:B$607, 2, FALSE) , ""N/A"")
End With

Awesome! This works great, thank you! Is it possible to adjust the A$2:B$607 to read past row 607 to the last row of data? There will be rows added but I don't want to make it read to row 2000 and make the macro run slower but I also don't want to have to adjust the macro every time information is added below row 607.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Sure, actually meant to add that but forgot - honest.😉
VBA Code:
Dim lrData As Long

lrData = Sheets("Leader Reference").Range("A" & Rows.Count).End(xlUp).Row

With Sheets("Master Sheet")
    .Range("L2:L" & .Range("K" & Rows.Count).End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(K2, 'Leader Reference'!A$2:B$" & lrData & ",2, FALSE) , ""N/A"")
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,122,352
Messages
5,595,657
Members
414,006
Latest member
Davefromlondon

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