Formula R1C1 in VBA - Strange Results (Dynamic)
 

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,

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,

Formula R1C1 in VBA - Strange Results (Dynamic)
 

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!

"Paul C" wrote:

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,