Absolute and Relative values in the same formula...

Posted by Dave on January 08, 2002 7:42 AM

HI all.

i have a macro which contains this line:

ActiveCell.FormulaR1C1 = "=SUM(?????:R[-1]C)"

i'd like to replace the ????? with something like "C2". In other words, sum the range from one cell up from the active cell to one down from the top of the column. I don't know if you can mix relative and absolute reference within a formula.

Any thoughts?

Cheers,
Dave

Posted by Mark W. on January 08, 2002 7:53 AM

=SUM(R2C3:R[-1]C) (nt)

Posted by Mark W. on January 08, 2002 7:56 AM

=SUM(R2C:R[-1]C) ...to work on any column (nt)

Posted by Mark W. on January 08, 2002 7:58 AM

If you're ever in doubt about R1C1 formulation...

just open a fresh worksheet, create your formula
using A1 style, and then using the Tools | Options...
menu command change the General Settings by
checking the "R1C1 reference style" check box.
Re-examine the formula... and there you go!

Posted by Dave on January 08, 2002 8:02 AM

Thanks, worked a treat!

Thanks a lot!
Worked a treat :)