PDA

View Full Version : VBA - Dodgy IF statement results from certain numbers



Checkerx
04-21-2010, 06:03 AM
I am doing a sum of length x width x price/m I then check to see if it is a whole number by doing (if round(total, 0) = total)

If it lands on a whole number (so say if length = 20 and width = 5); some prices (eg. 11.95 or 9.49) work fine and the IF statement says its true.
But certain numbers (eg. 8.96, 33.48, 18.99), even though it still equates to a whole number the IF statement returns it as false and performs the wrong functions.

So for example, 5 x 5 x 8.96 = 224. But then the line 'If Round(total, 0) = total' comes out as false

Does anyone know if there is something weird about these numbers that cause this error?


Here are the numbers I know that cause the problem: 8.96, 33.48,17.33, 17.99,18.99,19.99,37.02,40.91,32.70,68.01,37.60

And here are some numbers that work fine: 9.49,11.95,23.91,23.99,20.99,8.94,8.98,15.54,7.76,9.56

lucas
04-21-2010, 06:30 AM
Is this a formula? If so, what is the formula exactly?

Checkerx
04-21-2010, 06:55 AM
Is this a formula? If so, what is the formula exactly?
Prce1 = CDbl(Sheets("Carpet").Cells(Counter, 3).Value) * ((CDbl(txtLen1.Text)) * (CInt(cmbWid1.Text)))
If Round(Prce1, 0) = Prce1 Then
txtPrc1.Text = "£" & Prce1 & ".00"
Else
P2 = CStr(Prce1)
P2 = Split(P2, ".")(1)
If Len(P2) = 1 Then txtPrc1.Text = "£" & Prce1 & "0"
If Len(P2) = 2 Then txtPrc1.Text = "£" & Prce1
If Len(P2) > 2 Then
Prce1 = Prce1 + 0.01
Prce1 = Round(Prce1, 2)
txtPrc1.Text = "£" & Prce1
End If
End If
The error is that it finds it false and then tries to split using a decimal place that is not there