Vlookup help needed - Can't seem to find this answer elsewhere

0
votes

I'm trying to lookup an ID from column I, in column A. This is the code I'm using:

Dim x As Long

lr = Worksheets("Risk Explorer greeks").Cells(Rows.Count, "I").End(xlUp).Row

Range("J2:J2" & lr).FormulaR1C1 = "=VLOOKUP(RC[-1], R1C1:R50000C1, 1, False)"

I only have ~40,000 values in column J, however when I run this code it ends up populating all the way down to cell 237,000 - how can I amend the code so that it only looks up column J where it has a value, and doesnt lookup loads of blank cells?

Alternatively if there is a faster way to do this lookup rather than the above formula, then pls suggest - I'd be grateful for any help!

1
vbaexcel
I suggest you avoid using VLOOKUP by using an INDEX/MATCH instead. INDEX/MATCH much faster than VLOOKUP when dealing with a large number of cells. So, change "=VLOOKUP(RC[-1], R1C1:R50000C1, 1, False)" for "=INDEX( R1C1:R50000C1,MATCH(RC[-1], R1C1:R50000C1,0))". – Gravitate

1 Answers

4
votes

You're appending the number 37000 to the string "J2:J2", which gives you "J2:J237000".

Replace Range("J2:J2" & lr) with Range("J2:J" & lr). You should be good to go.