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

 

[JustynaMK]

Hi,

Try to use the following code:

Private Sub BalanceSoFar_Click()
    Dim lngLastRow          As Long
    Dim lngSumValues        As Long
    
    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]

Many thanks it worked well.

 

[ipbr21054]

Hi,

Try to use the following code:

Private Sub BalanceSoFar_Click()
    Dim lngLastRow          As Long
    Dim lngSumValues        As Long
    
    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

Hi,
I spoke too son.

I have check the sheet name & cell range of which is correct.

When the code is run it returns £0.00

I then tried it out with a run on the same sheet etc but still returns £0.00

Do you see a reason why ???

 

[Fluff]

Check that your numbers are actually numbers & not text.
In a blank cell enter =ISNUMBER(O6) What does it say?

 

[ipbr21054]

Placing that code in say Z1 it reads TRUE

 

[Fluff]

What is the value in O6?

 

[ipbr21054]

£50.00

 

[ipbr21054]

Fluff,

Please use the code supplied on a new excel sheet & see if you also get £0.00

 

[Fluff]

There's nothing wrong with the code, that's why I was asking if your numbers were true numbers.
I cannot think of any reason why the code would give £0.00
That said as you are using decimals I'd make this change

Dim lngSumValues        As Double