VLookup VBA Loop

[Jones1413]

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.

 

[offthelip]

try this code which uses variant arrays and is much faster than using vlookup, but it does the same thing:

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]

You don't need a loop for that.

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]

You don't need a loop for that.

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]

Sure, actually meant to add that but forgot - honest.?

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