Fix an Excel Recorded Macro That Copies Values Instead of Filling Formulas
Question details
The user needs a recorded Excel macro to fill formulas down a range while adjusting row references for each destination row.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro is recorded or run to extend a formula from one row to multiple rows.
- Observed behavior
- Each row displays the same first calculated result because the macro copies values or uses fixed references instead of filling relative formulas.
Identify the source cell containing the correct formula and the last row that should receive it. In the VBA editor, check whether the recorded code uses .Value, .Value2, or absolute cell references that prevent the formula from adjusting.
Use AutoFill to extend the source formula with relative references
Use this method when one cell already contains the correct formula and you need Excel to adjust relative row references as it fills down.
AutoFill copies the formula pattern rather than the displayed result. For example, a formula in B2 that refers to A2 becomes a formula referring to A3 in B3, A4 in B4, and so on.
In Excel, press Alt+F11, then open the module that contains the recorded macro from the Project Explorer.
Look for statements such as Range("B2").Value = Range("B2").Value or Selection.Copy followed by PasteSpecial xlPasteValues, and replace them if the goal is to fill formulas.
Use a formula source cell and destination range, for example: Range("B2").AutoFill Destination:=Range("B2:B100").
Press F5 in the VBA editor or use Excel's Developer tab > Macros > select the macro > Run. Check that each row contains an adjusted formula rather than the same calculated value.
Assign the formula to the full destination range
Use this approach when you want the macro to define the formula directly instead of relying on a pre-existing formula cell.
Fill Relative Formulas Down a Column with WPS Spreadsheets
WPS Spreadsheets lets you extend a formula down a selected range without converting it to static values. Use the fill handle or the Fill Down command to preserve formulas and automatically adjust relative cell references by row.
- 1. Enter the source formula: In WPS Spreadsheets, type the required formula in the first destination cell, such as B2.
- 2. Select the fill range: Select the formula cell and all cells below it that should receive the formula, for example B2:B100.
- 3. Fill the formula down: Go to the Home tab, choose Fill, and select Down to copy the formula pattern through the selected cells.
- 4. Check adjusted references: Click several filled cells and review the formula bar to confirm that references changed by row, such as A2, A3, and A4.
Frequently Asked Questions
Why does my recorded Excel macro put the same result in every row?
The macro is likely copying the displayed value with .Value, .Value2, or PasteSpecial xlPasteValues. Replace value-copying code with AutoFill or assign a formula using the .Formula property.
What VBA code fills a formula from B2 down to B100?
Use Range("B2").AutoFill Destination:=Range("B2:B100"). The formula in B2 must contain relative references if those references should change in each row.
How can I make a VBA formula reference change for every row?
Assign the formula to the full range with relative references, for example Range("B2:B100").Formula = "=A2*10". Excel adjusts A2 for each row in the destination range.
When should I use $ in an Excel formula used by a macro?
Use $ only for references that must stay fixed, such as $D$1. Do not use $ before a row or column reference that needs to move as the formula is filled down.
Can I fill formulas down without using an Excel macro?
Yes. Select the formula cell and the cells below it, then use Home > Fill > Down. You can also drag or double-click the fill handle when adjacent data defines the fill length.
