Sum unknown length column data in VBA

[John]

All, I really appreciate the help from this group of talented people.

I want to add a formula in the 6th column, one row after the bottom
row of data, summing from F5 to F "bottom".
I know I'm close, but something here is wrong.

Sub ColTots()

Dim z As Long
Dim bottom As Long

bottom = Cells(Rows.Count, "B").End(xlUp).Row
z = bottom + 1

cells(z,6).formula = "=SUM("F5:F" & bottom)"


Thanks
john

 

[Norman Jones]

Hi John,

Try:

Cells(z, 6).Formula = "=SUM(F5:F" & bottom & ")"

 

[John]

Sub Macro1()
'
' Macro1 Macro
'
Range("F5").End(xlDown).Name = "finish"
Range("finish").Offset(1, 0).Formula = "=SUM
(f5:finish)"
End Sub

 

[keepITcool]

as a (simpler?) alternative I'd use:

.formular1c1 = "=sum(r5c:r[-1]c)"

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >