Formula R1C1 in VBA - Strange Results (Dynamic)

[Louise]

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,

 

[Paul C]

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

 

[Louise]

Great thank you, I did have the logic right then just didn't account for the
row change where I was inputting... Appreciate your help!

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.

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,