Code to sum columns in lastrow +1

[Klartigue]

I have a list of quantities in column F. I would like to write a code to say sum the numbers in column F and place that sum in column F in two cells below the lastrow.

Any ideas?

[snb]

Sub M_snb()
msgbox "Please enter the sum of values in column 6 into " & cells(columns(6).specialcells(2).count+2,6).address
End Sub

[Klartigue]

Sorry I didn't mean actually write a message. I need a formula that sums cell "F1"- down to cell "F lastrow" and place that sum in cell "F lastrow +2"

[JKwan]

give this a spin


Sub SumToLastRow()
Dim LastRow As Long
Dim WS As Worksheet

Set WS = ThisWorkbook.Worksheets("Sheet1")
LastRow = FindLastRow(WS, "F")
WS.Range("F" & LastRow + 2).Formula = "=sum(F1:F" & LastRow & ")"

Set WS = Nothing
End Sub
Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function

[snb]

Humor isn't your strongest property ?? :biggrin:

[Klartigue]

give this a spin


Sub SumToLastRow()
Dim LastRow As Long
Dim WS As Worksheet

Set WS = ThisWorkbook.Worksheets("Sheet1")
LastRow = FindLastRow(WS, "F")
WS.Range("F" & LastRow + 2).Formula = "=sum(F1:F" & LastRow & ")"

Set WS = Nothing
End Sub
Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function



That doesn't seem to do anything - I am not sure how to be using the function end function part.

[Klartigue]

Sub SumCashTotal()
Dim LastRow As Long
Dim WS As Worksheet

With ActiveSheet

LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

.Range("F" & LastRow + 2).Formula = "=sum(F1:F" & LastRow & ")"

End With

End Sub


I used the above and it works, thanks all!

[mikerickson]

If you want a value

With Range("F:F")
.Cells(Rows.Count,1).End(xlUp).Offset(2,0).Value = WorksheetFunction.Sum(.Cells)
End With

If you want a formula

With Range("F:F")
.Cells(Rows.Count,1).End(xlUp).Offset(2,0).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
End With