Dear All,

I have a variable range of data in a column. Exactly at
the bottom of this column I need to insert a SUM function
for the numbers above (not just calculate the totals). I
tried
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0).Formula
= "=SUM(a2:(Range("a6").End(xlDown))"
but it's not working

Please advise!

Thanks.

Denis Petrov

Denis,

I like to work with R1C1 notation:
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0).FormulaR1C1 _
= "=SUM(R2C:R[-1]C)"

Since there is no number after 'C', the formula is generic for any column.
And it doesn't matter if your worksheet displays columns with alpha or
numeric.

--
sb
"Denis Petrov" wrote in message
...
Dear All,

I have a variable range of data in a column. Exactly at
the bottom of this column I need to insert a SUM function
for the numbers above (not just calculate the totals). I
tried
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0).Formula
= "=SUM(a2:(Range("a6").End(xlDown))"
but it's not working

Please advise!

Thanks.

Denis Petrov

ActiveSheet.Range("a6").End(xlDown).Offset(1, 0).Formula _
= "=SUM(a2:" & Range("a6").End(xlDown).Address & ")"

or

Activesheet.Range("A6").End(xldown).Offset(1,0).Fo rmulaR1C1 = _
"=Sum(R2C:R[-1]C)"

--
Regards,
Tom Ogilvy


"Denis Petrov" wrote in message
...
Dear All,

I have a variable range of data in a column. Exactly at
the bottom of this column I need to insert a SUM function
for the numbers above (not just calculate the totals). I
tried
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0).Formula
= "=SUM(a2:(Range("a6").End(xlDown))"
but it's not working

Please advise!

Thanks.

Denis Petrov

To add to what you have gotten, if you want just the sum without the formula
Sub sumem()'from a2 to a2 end xl down
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0) = _
Application.Sum(Range(Cells(2, 1), Cells(Range("a6").End(xlDown),1)))
End Sub

"Denis Petrov" wrote in message
...
Dear All,

I have a variable range of data in a column. Exactly at
the bottom of this column I need to insert a SUM function
for the numbers above (not just calculate the totals). I
tried
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0).Formula
= "=SUM(a2:(Range("a6").End(xlDown))"
but it's not working

Please advise!

Thanks.

Denis Petrov