Formula R1C1 in VBA - Strange Results (Dynamic)
are you sure the 3rd formula is OK
All three of these formuls seem to be set up to reference the desired ranges
from row 45
45-34=11 45-17=28 Good for row 45 and would be SUM(I11-I28)/I28
45-32=13 45-15=30 Good for row 45 and would be SUM(I13-I30)/I3
To put the formula in Row 46 you need to change the math a little
46-33=13 46-16=30, so your formula for would need to read
"=SUM(R[-33]C-R[-16]C)/R[-16]C"
The same logic would follow for the 3rd formula which is intended to go in
row 47
--
If this helps, please remember to click yes.
"Louise" wrote:
Hi,
I have the following code in VBA to give me some variance % results.
Although the rows are always static the columns are not:
ActiveCell.FormulaR1C1 = "=SUM(R[-34]C-R[-17]C)/R[-17]C"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-32]C-R[-15]C)/R[-15]C"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-30]C-R[-13]C)/R[-13]C"
What I want this to do is to start in row 45, in the column i am in (worked
out prior to this in the macro) and put in a formula which basically in Excel
would read:
=SUM(I11-I28)/I28
then down one row
=SUM(I13-I30)/I30
then down one row
=SUM(I15-I32)/I32
This works, but only for the 1st and 3rd formulae, the second one is always
wrong by one row...
Am I doing something majorly wrong here?
Thanks,
|