Excel - VBA r1c1 absolute previous row in formula

Asked By SLP on 06-Nov-09 10:29 AM
Hi,  I get an error message when I run this code:

ActiveCell.FormulaR1C1 = "=round(R-1C*RC[7],2)"

I need to use the cell directly above the cell the where the pointer is.  If
I do not make it an absolute reference it works fine but I need it to be an
absolute as the formula gets copied down.  Also, this gets used threw out the
routine so I cannot hard set the cell above.

Thanks.




Barb Reinhardt replied on 06-Nov-09 10:43 AM
ActiveCell.FormulaR1C1 = "=round(R[-1]C*RC[7],2)"
Niek Otten replied on 06-Nov-09 10:47 AM
ActiveCell.FormulaR1C1 = "=round(R" & activecell.row & "C*RC[7],2)"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Niek Otten replied on 06-Nov-09 12:01 PM
of course that should have been

ActiveCell.FormulaR1C1 = "=round(R" & activecell.row - 1 & "C*RC[7],2)"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel