VBA & Macro Problems

Fix an Excel VBA VLOOKUP Formula with an R1C1 Range Reference

Muhammad TalhaMuhammad Talha Nov 18, 2026 868 views

Question details

The user needs to correct a VBA VLOOKUP formula that mixes R1C1 and A1-style references.

Fix an Excel VBA VLOOKUP Formula with an R1C1 Range Reference
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the macro

Open the VBA editor and locate the procedure that assigns ActiveCell.FormulaR1C1.

2
Replace the formula

Use this assignment: ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'old'!R1C15:R100000C16,2,FALSE)".

3
Run and verify

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 R1C1 notation throughout the VBA formula
Qualify the destination when possible: To avoid depending on the active selection, replace ActiveCell with a qualified destination such as ThisWorkbook.Worksheets("Results").Range("H2"), adjusting the sheet and cell to match your workbook.
Free Microsoft Office alternative

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.

Enter and review VLOOKUP formulas in WPS SpreadsheetOpen and edit Microsoft Excel XLSX filesNavigate a familiar spreadsheet interfaceUse a free, lightweight Office alternative
microsoft office alternative - wps office

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.