Revision history [back]

To incorporate a dynamic VLOOKUP into FormulaR1C1 using VBA, follow these steps:

1. Define the range of the lookup table and the lookup value cell. For example:

Dim lookupTable As Range
Set lookupTable = Range("A1:B10")

Dim lookupValue As Range
Set lookupValue = Range("C1")

1. Use the MATCH function to find the row number of the lookup value in the first column of the lookup table. For example:

Dim rowNumber As Long
rowNumber = Application.WorksheetFunction.Match(lookupValue.Value, lookupTable.Columns(1), 0)

1. Construct the VLOOKUP formula using the row number as the index number argument. For example:

Dim vlookupFormula As String
vlookupFormula = "=VLOOKUP(" & lookupValue.Address(False, False) & "," & lookupTable.Address(False, False) & ",2,FALSE)"

1. Set the formula of the desired cell using FormulaR1C1. For example:

Range("D1").FormulaR1C1 = vlookupFormula

1. Optionally, convert the formula to static values. For example:

Range("D1").Value = Range("D1").Value

By following these steps, you can dynamically incorporate a VLOOKUP formula into FormulaR1C1 using VBA.