Hi
I'm trying to use a relative formula in a Next loop where a variable changes in the formula.
Here is the unchanged formula:
ActiveCell.FormulaR1C1 = _
'=SUMPRODUCT(RC[-5]:R[1]C[-5],RC[-4]:R[1]C[-4])/SUM(RC[-4]:R[1]C[-4])'
And I would like to change it to:
ActiveCell.FormulaR1C1 = _
'=SUMPRODUCT(RC[-5]:R[X]C[-5],RC[-4]:R[X]C[-4])/SUM(RC[-4]:R[X]C[-4])'
Where X is a variable and it changes (by 1) each time the loop is run.
How can I do this? I keep getting a 'Run-time error '1004': Application-defined or object-defined error'.
What am I missing?
Cheers
pvr928
In VBA, cell selections are recorded as absolute references unless you click the Relative Reference button on the Stop Recording toolbar. If you click this button, the recorder begins recording relative references. If you click the button again, the recorder records subsequent actions using absolute references and so on. During macro recording cell references can be made either relative to the start position or with an absolute address. By default, recorded macros use absolute.