Fix an Excel VBA VLOOKUP Formula with an R1C1 Range Reference
Question details
The user needs to correct a VBA VLOOKUP formula that mixes R1C1 and A1-style references.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro assigns a VLOOKUP formula using FormulaR1C1 and looks up a value in a table on the old worksheet.
- Observed behavior
- The formula uses an A1-style lookup-table range with FormulaR1C1 and can produce an invalid reference or a #NAME? error.
Open the workbook and check the destination cell, the lookup value's position, and the table columns on the old worksheet. The examples use columns O:P as the lookup table and a lookup value seven columns to the left of the destination cell.
Use R1C1 notation throughout the VBA formula
Use this approach when the macro assigns the formula through FormulaR1C1.
In R1C1 notation, RC[-7] refers to the cell seven columns left of the formula cell. R1C15:R100000C16 refers to the absolute range O1:P100000.
Open the VBA editor and locate the procedure that assigns ActiveCell.FormulaR1C1.
Use this assignment: ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'old'!R1C15:R100000C16,2,FALSE)".
Select the intended destination cell, run the macro, and check the resulting formula and lookup value in Excel. The destination must be in column H or farther right for RC[-7] to be valid.

Use Formula with A1-style references
Use this alternative when you prefer to write the lookup table and lookup value in A1 notation.
Work with VLOOKUP formulas in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easy to open and work with existing files. It does not fix Excel's VBA formula assignment; check macro compatibility before migrating automated workbooks.

Frequently Asked Questions
Why does an A1-style range cause trouble in FormulaR1C1?
FormulaR1C1 expects cell and range references in R1C1 notation. Replace an A1 range such as O1:P100000 with R1C15:R100000C16, or use the Formula property for A1 notation.
What does RC[-7] mean in the VLOOKUP formula?
It refers to the cell in the same row and seven columns to the left of the formula cell. For example, in H2 it refers to A2.
What should I check if the formula still returns #NAME?
Inspect the formula in the destination cell and verify the function name, sheet name, and reference syntax. Also confirm that the macro uses FormulaR1C1 for R1C1 references or Formula for A1 references.
Why should I avoid ActiveCell in the macro?
ActiveCell depends on the user's current selection. Use a qualified destination such as ThisWorkbook.Worksheets("Results").Range("H2") when the output should always go to a specific cell.




