Help with renaming in Visual Basic
 

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

Help with renaming in Visual Basic
 

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

"Alberta Rose" wrote:

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

Help with renaming in Visual Basic
 

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.




Alberta Rose wrote:

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

--

Dave Peterson