Find last row in Column and Sum the last 4 rows

[grady121]

Hi, I'm trying to get the total of the last 4 values in Column "I".
I then want to enter the sum value into a New Note in the same last cell, using a specific text string.

I can already find the row and select the cells I need, but the value keeps coming back as "0".

    Dim MySum As Long
    Dim rng As Range
        Cells(Rows.Count, "I").End(xlUp).Offset(-3, 0).Select
        Selection.Resize(Selection.Rows.Count + 3).Select
        Set rng = Selection
        MySum = WorksheetFunction.Sum(Range(rng.Address))
    
    MsgBox MySum
        Cells(Rows.Count, "I").End(xlUp).Select
        Selection.AddComment 'Insert Charges & Fees Comment
        Selection.Comment.Visible = False
        Selection.Comment.Text Text:="Total Annual Charges" & Chr(10) & "&  Fees = " & "£" & MySum & " pa."
        ActiveCell.Comment.Shape.TextFrame.AutoSize = True

Any help appreciated

 

[MARK858]

I can already find the row and select the cells I need, but the value keeps coming back as "0".

The sum part of your code works fine for me, what do you get with the code below? are all your numbers whole numbers and not just decimals?

Sub zzzxx()
    Dim MySum As Long
    Dim rng As Range

        Set rng = Cells(Rows.Count, "I").End(xlUp).Offset(-3, 0).Resize(4)
        MySum = WorksheetFunction.Sum(rng)
  
    MsgBox MySum
End Sub

 

[duggie33]

You could try this...change the sheet reference to whatever suits your data.

Sub SumLastFour()

    Dim MySum As Double
    Dim rngLast As Range
    Dim rngSum As Range

    Set rngLast = Sheet8.Cells(Rows.Count, "I").End(xlUp)
    Set rngSum = Sheet8.Cells(Rows.Count, "I").End(xlUp).Offset(-3, 0).Resize(4, 1)

    MySum = WorksheetFunction.Sum(rngSum)

    With rngLast
        .AddComment
        .Comment.Visible = False
        .Comment.Text Text:="Your name:" & Chr(10) & "The sum is " & MySum
    End With

End Sub

Hope that helps,

Doug

 

[grady121]

Thanks for both your replies, but unfortunately each showed errors at some point.

This prompted me to examine my formatting of the sheet and the values I was entering via a UserForm.
It turns out that although I had the cells formatted for currency, the UserForm I updated a few months back, was amended to show a currency symbol to the Textbox value.... The end result is that it appeared exactly the same on the sheet, but not good for summing up the 4 values...

So it turns out that the additional symbol, was the route of my problems.

Thanks again.