Search code examples
excelvbavlookup

Creating a vlookup formula with Excel VBA

I have the following VBA code (a loop):

For i=1 to 20
Range("K" & i).FormulaR1C1 = "=VLOOKUP(RC[1],Database!R2C1:R10000C2,2,FALSE)"
Next i

I would like to make the second argument (the table where the value is taken from) more dynamic.

Suppose in the very beginning of the code I have:

Dim MyLookupRange as Range
Set MyLookupRange = Sheets("Database").Range("A2:B10000")

How can I use MyLookupRange as the second argument in my vlookup formula in my loop?

Thank you!

Solution

  • Tecnically in your loop you could use something like this:

    For i = 1 To 20
    Range("K" & i).FormulaR1C1 = "=VLOOKUP(RC[1]," & MyLookupRange.Address(True, True, xlR1C1, True) & ",2,FALSE)"
Next i

    Then again there is no need for a loop. Something like this should suffice:

    Range("K1:K20").FormulaR1C1 = "=VLOOKUP(RC[1]," & MyLookupRange.Address(True, True, xlR1C1, True) & ",2,FALSE)"