VBA Drag down Vlookup

Question

All,

I have a data sheet of around 1.000 values which need a matching amount (the amount of valueschanges every day) . These amounts can be found in another tab "Data".

so using a Vlookup code in VBA should help me. The code I'm using is:

Sheets("Data").Range("E2") = Application.WorksheetFunction.VLookup(Sheets("Data").Range("D2"), Sheets("Blocked").Range("C:D"), 2, False)

Result should appear in column E responding with the row of the lookup_value which can be found in column D.The table and column index don't change being Sheets("Blocked").Range("C:D")and 2

This code gives me the result I wanted but as I tried to drag down the formula with this function: Range("E2").AutoFill Destination:=Range("E2:E440")

How can I drag down this formula without needing to create a seperate vlookup for each row?

You need to convert your Formula to R1C1 type and then paste the formula as is in your range E2 rather than the result. Alternatively u can use a loop — izzymo
Thanks for the fast response @izzymo, I will look into those options. — Epil epic

izzymo izzymo · Accepted Answer · 2016-03-09T08:46:48

You should use the Formula as an R1C1 reference and then u will be able to drag down

Sheets("Data").Range("E2").FormulaR1C1 = "=VLookup('Data'!R[-1]C,'Blocked'!C[-2]:C[-1],2,0)"

VBA Drag down Vlookup

1 Answers