I have a small piece of code in my program that looks like this:

Range("J1").Select 'Make Cell J1 Active
ActiveCell.FormulaR1C1 = "=SUM(R[4]C:R[504]C)" 'Calc. sum for Cells J5 to
J505

However I now need to put the result of the Autosum into a variable.
I have used:
Range("J1).Select
Variable = ActiveCell

and

Variable = ActiveCell.FormulaR1C1 = "=SUM(R[4]C:R[504]C)"

But neither of these seem to work as the variable is still empty.

Can anyone point me in the right direction?

Thanks

Neil