Help with renaming in Visual Basic

[Alberta Rose]

I have recorded a macro to do mathmatical calculations. When I go into
Visual Basic, the recorder has put it's own language where my cell reference
would be. See example below:


"=IF(R[-4]C[-1]=0,0,SUMIF(R[-7]C[-30]:R[65491]C[-30],RC[-3],R[-7]C[-17]:R[65491]C[-17])/R[-4]C[-1])"

I want to change it to reference the actual cells, but when I change for
example the R[-4]C[-1]=0 to AC4-0, the macro will not return any values,
just gives me a #NAME? error message. What am I missing here?

Thanks..
Laurie

 

[Barb Reinhardt]

It's using RC notation. For example,

R[-4]C[-1] is 4 rows up and 1 column to the left of the current cell. I'm
not sure there is any way around using this in VBA.

HTH,
Barb Reinhardt

 

[Dave Peterson]

The macro recorder recorded your formula in R1C1 reference style.

You can see this if you use this (in xl2003 menus):
Tools|Options|General tab|check r1c1 reference style

The column letters will become numbers.

You may like how this works.

R5C3 is the same as $C$5
R[5]C[3] is 5 rows down from the current row and 3 columns to the right of the
current column.

This works very nicely if your formula always referred to cells on the same row.
=rc[3]+rc[-2]
would add the cell 2 columns to the left with the cell three columns to the
right.

To make your R1C1 formula work in your code, you may be able to just use:

SomeCell.formulaR1C1 = "=IF(R[-4]C[-1]=0,0,SUM.....

Instead of using the .formula property.

I have recorded a macro to do mathmatical calculations. When I go into
Visual Basic, the recorder has put it's own language where my cell reference
would be. See example below:


"=IF(R[-4]C[-1]=0,0,SUMIF(R[-7]C[-30]:R[65491]C[-30],RC[-3],R[-7]C[-17]:R[65491]C[-17])/R[-4]C[-1])"

I want to change it to reference the actual cells, but when I change for
example the R[-4]C[-1]=0 to AC4-0, the macro will not return any values,
just gives me a #NAME? error message. What am I missing here?

Thanks..
Laurie