Getting value of an Autosum

[Neil]

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

 

[Tom Ogilvy]

Dim dblSum as Double
dblSum = Application.WorksheetFunction.Sum( Range("J5:J505"))
msgbox format(dblSum,"#0.00")