Autosum & Msgbox

[ipbr21054]

Hi,

I have this code supplied below in place but need advice please.

I would like to include the autosum shown below into it.

Worksheet is called DATABASE
The range would be O5:O179

As info is added to the DATABASE worksheet the 179 then becomes 180,181,182 etc etc

Many thanks.

Private Sub BalanceSoFar_Click()MsgBox "Earnings To Date " & Format(Worksheets("DATABASE").Range("A1").Value, "£0.00")
End Sub

 

[ipbr21054]

Sorry to say its still the same.
I will drop this now as taking up too much time.

Many thanks anyway.

Did you see the post today where it was mentioned that you dont download from the internet ?

 

[ipbr21054]

Just an update,

If i use the below it returns correctly.

=SUM(DATABASE!O6:O179)

If however i use this then it fails

=SUM(DATABASE!O6:O)

 

[MARK858]

If however i use this then it fails
Code:
=SUM(DATABASE!O5:O)

Why would you expect that to work without an end row number?

 

[ipbr21054]

My first post showed O179 but then if you look at post 2 i then went from there.

 

[MARK858]

My first post showed O179 but then if you look at post 2 i then went from there.

The codes suggested to you used lngLastRow as a variable number for the last used row

(Range("O5:O" & lngLastRow))

In your post number 12 you are omitting it so the question remains why you think it should work without an end row number?

I would check with the code JustynaMK posted what lngLastRow is returning judging by your last statement as you probably need to get the last row from another column.

 

[MARK858]

@ipbr21054, what happens with the code below?

Private Sub BalanceSoFar_Click()
    Dim lngLastRow          As Long
    Dim lngSumValues        As Double
    
    With Sheets("DATABASE")
        lngLastRow = .Cells(.Rows.Count, "O").End(xlUp).Row
        lngSumValue = Application.WorksheetFunction.Sum(.Range("O5:O" & lngLastRow))
    End With
    
    MsgBox "Earnings To Date " & Format(lngSumValue, "£0.00")


End Sub

 

[ipbr21054]

Thanks very much MARK858

It works as now expected.

 

[MARK858]

You're welcome. Just so you know the sum was missing a period/fullstop before the Range so it wasn't linked to the with statement so unless Sheets("DATABASE") was the activesheet you would get the error.

 

[ipbr21054]

Thanks for explaining.