Decimal values ignored by script

[Alex Piotto]

' Morning everyone...

I am making a sum of numbers (negatives in that case) from a column.
The numbers are formatted. Like 1,250.55...
I show the result in a textbox. The result is formatted too. Like 1,250.55...

'SUM NEGATIVES AND PUT RESULT IN TEXTBOX4
Dim x As Long
Dim c As Range
Dim tot As Integer
x = Cells(Rows.Count, "B").End(xlUp).Row
tot = 0
For Each c In Range("H14:H" & x)
If c.Value < 0 Then
tot = tot + c.Value
End If
Next c
ActiveSheet.TextBox4.Value = tot
ActiveSheet.TextBox4.Value = Format(TextBox4.Value, "#,##0.00")

But the decimals are always zero zero. There is a rounding up playing here without asking....

How to avoid it?

 

[mohadin]

Hi

Dim tot As Integer

tot is integer
so you have to

Dim tot As Double

 

[Alex Piotto]

Fast as a flash! Thanks a lot.

 

[mohadin]

You are welcome
and thank you for the feedback
Be happy

 

[Alex Piotto]

May I ask something more?
If there are no values I get an error... If I do what i put in green it is going to work?

'SUM NEGATIVES AND PUT RESULT IN TEXTBOX4
Dim x As Long
Dim c As Range
Dim tot As Double
x = Cells(Rows.Count, "B").End(xlUp).Row
tot = 0

' if c = "" then exit sub else

For Each c In Range("H14:H" & x)
If c.Value < 0 Then
tot = tot + c.Value
End If
Next c

' end if

 

[mohadin]

Well
Try

Dim x As Long
Dim c As Range
Dim tot As Double
x = Cells(Rows.Count, "B").End(xlUp).Row
tot = 0
For Each c In Range("H14:H" & x)
    If c <> "" And c.Value < 0 Then
        tot = tot + c.Value
    End If
Else
    tot = 0
End If
Next c

 

[Alex Piotto]

One End If too much...

 

[mohadin]

Sorry

Dim x As Long
Dim c As Range
Dim tot As Double
x = Cells(Rows.Count, "B").End(xlUp).Row
tot = 0
For Each c In Range("H14:H" & x)
    If c <> "" And c.Value < 0 Then
        tot = tot + c.Value
    End If
Else
    tot = 0
End If
Next c

 

[Alex Piotto]

It is the same script ...

 

[mohadin]

Dim x As Long
Dim c As Range
Dim tot As Double
x = Cells(Rows.Count, "B").End(xlUp).Row
tot = 0
For Each c In Range("H14:H" & x)
    If c <> "" And c.Value < 0 Then
        tot = tot + c.Value

Else
    tot = 0
End If
Next c