Decimal values ignored by script

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
' 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...

VBA Code:
'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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
929
Office Version
  1. 2013
Platform
  1. Windows
Hi
VBA Code:
Dim tot As Integer
tot is integer
so you have to
VBA Code:
Dim tot As Double
 
Solution

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
929
Office Version
  1. 2013
Platform
  1. Windows
You are welcome
and thank you for the feedback
Be happy
 

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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?

VBA Code:
'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-known Member
Joined
Mar 22, 2015
Messages
929
Office Version
  1. 2013
Platform
  1. Windows
Well
Try
VBA Code:
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

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

One End If too much...
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
929
Office Version
  1. 2013
Platform
  1. Windows
Sorry
VBA Code:
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
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
929
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,240
Messages
5,600,491
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top