VBA & Macro Problems

Fix Relative References and Autofill in an Excel VBA Macro

Maira MehtabMaira Mehtab Nov 18, 2026 869 views

Question details

Update an Excel VBA macro so formulas reference the correct starting cell in each data section.

Fix Relative References and Autofill in an Excel VBA Macro
Product
Microsoft Excel
Device & OS
not provided
Scenario
A recorded macro fills formulas across multiple data sections that each need their own fixed reference.
Observed behavior
The macro continues to use the original cell reference instead of updating it for each section.
Before you start

Identify the cells that mark each section, the formula range, and the reference that should stay fixed within each section. Open the VBA editor and check whether the recorded macro hard-codes a reference such as $B$1.

Solution 1Recommended

Build Section-Specific Formulas with VBA

Use this approach when every section needs a different fixed reference and the same formula pattern.

The example below treats each nonempty cell in column B as a section marker. For each section, it fills column D below the marker with a formula that multiplies the value in column C by the marker cell in column B.

The example assumes column C contains data through the final section and that column D is the intended output range. It overwrites existing contents in that range; adapt the columns and formula to your worksheet before running it.

1
Open the VBA editor

In Excel, open Developer > Visual Basic, then select Insert > Module. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Add the section-based procedure

Paste this example into the module: Sub UpdateSectionFormulas() Dim ws As Worksheet Dim lastRow As Long, r As Long, startRow As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row startRow = 0 For r = 1 To lastRow If Len(ws.Cells(r, "B").Value) > 0 Then If startRow > 0 And r > startRow + 1 Then ws.Range(ws.Cells(startRow + 1, "D"), ws.Cells(r - 1, "D")).FormulaR1C1 = "=RC[-1]*R" & startRow & "C2" End If startRow = r End If Next r If startRow > 0 And lastRow > startRow Then ws.Range(ws.Cells(startRow + 1, "D"), ws.Cells(lastRow, "D")).FormulaR1C1 = "=RC[-1]*R" & startRow & "C2" End If End Sub

3
Adapt and run the macro

Change the marker column, output range, and FormulaR1C1 expression to match your data. Select the intended worksheet, then use Developer > Macros to run UpdateSectionFormulas.

4
Check formulas in each section

Click cells in column D in several sections and inspect the formula bar. The column C reference should follow each row, while the column B reference should point to that section's marker row.

Build Section-Specific Formulas with VBA
Save the macro: Use File > Save As and select Excel Macro-Enabled Workbook (.xlsm) to keep the VBA code.
Free Microsoft Office alternative

Work with Spreadsheet Formulas in WPS Office

WPS Office is a free, lightweight alternative for working with Office files in a familiar interface. Open compatible Excel workbooks to review and edit formulas as you move between suites; Excel-specific VBA behavior may differ and should be tested separately.

Review and edit spreadsheet formulas in WPS SpreadsheetsCompatible with common Microsoft Excel .xlsx filesFamiliar spreadsheet interface for an easier transitionFree and lightweight for everyday Office tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does my recorded macro keep referring to the original cell?

A recorded formula may contain an absolute reference such as $B$1, which stays fixed when the formula is filled. Inspect the code in Developer > Visual Basic and construct the reference from the current section's row instead.

How does FormulaR1C1 help with autofill?

FormulaR1C1 lets you specify relative and fixed references explicitly. In the example, RC[-1] refers to column C on the current row, while R followed by the section row and C2 fixes the reference to column B.

Can I use Find instead of looping through rows?

Yes. VBA's Range.Find can locate section markers, and FindNext can locate subsequent matches. Save the first match's address and stop when the search returns to it, because Find wraps around the range.

How do I keep the macro when I save the workbook?

Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx workbook cannot retain VBA code.