Sum a variable length column

[L. Howard]

What little tidbit am I overlooking here.
rangeC selects the correct range.
rSum is the correct cell (First empty cell below the last entry in col C)

Returns #NAME error
Also tried this but does not compile rSum.Formula = "=Sum("C2:C" & lr)"

Thanks,
Howard

Option Explicit

Sub summerup()
Dim rangeC As Range
Dim rSum As Range
Dim lr As Long

lr = Cells(Rows.Count, 3).End(xlUp).Row
Set rangeC = Range("C2:C" & lr)

Set rSum = Range("C" & lr + 1)
'rangeC.Select
rSum.Formula = "=Sum(rangeC)"
End Sub

 

[L. Howard]

What little tidbit am I overlooking here.

rangeC selects the correct range.

rSum is the correct cell (First empty cell below the last entry in col C)



Returns #NAME error

Also tried this but does not compile rSum.Formula = "=Sum("C2:C" & lr)"



Thanks,

Howard

Was able to solve with this.

Option Explicit

Sub totColC()
Dim lrC As Long
lrC = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
Sheets("Sheet1").Range("C" & lrC + 1) = _
Application.WorksheetFunction.Sum(Sheets("Sheet1").Range("C2:C" & lrC))
End Sub

Howard