Fix Relative References and Autofill in an Excel VBA Macro
Question details
Update an Excel VBA macro so formulas reference the correct starting cell in each data section.

- 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.
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.
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.
In Excel, open Developer > Visual Basic, then select Insert > Module. If the Developer tab is hidden, enable it in Excel's ribbon settings.
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
Change the marker column, output range, and FormulaR1C1 expression to match your data. Select the intended worksheet, then use Developer > Macros to run UpdateSectionFormulas.
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.

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.

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.




