Problem with decimals in VBA code

[chiidzzz]

Hi, there is something that I can't figure out in this code,

Sub test()
Dim i As Integer
Dim diff05 As Double
diff05 = 0.8
        For i = 2 To 4
            If Sheet1.Cells(i, 2).Value >= diff05 And Sheet1.Cells(i, 2).Value < diff05 + 0.05 Then
                    Sheet1.Cells(i, 11) = Sheet1.Cells(i, 2).Offset(0, 6)
            End If
       Next


End Sub

In row 3, the value of cell(3,2) is 0.85
Logically, the code should read sheet1.cells(3,2)>= diff05 "as true" and sheet1.cells(3,2)< diff05 + 0.05 "as false"
But it is returning sheet1.cells(3,2)< diff05 + 0.05 "as true"
If I remove diff05+0.05 and I just put 0.85, it reads "false", what may be the problem?

 

[Joe4]

The problem is your order of operations. You need parentheses in it like this:

If Sheet1.Cells(i, 2).Value >= diff05 And Sheet1.Cells(i, 2).Value < (diff05 + 0.05) Then

Otherwise, if will compare the value in Cells(i,2) to diff05, and after it does that comparison, then it will add 0.05.
That is not what you want. You want it to compare the value to diff05 + 0.05. So you need to use parentheses around that addition.

 

[jkpieterse]

Incorrect, comparison operators are already treated last in the chain. The problem here is an IEE rounding error.

Sheet1.Cells(i, 2).Value-(diff05+0.05)

yields this value: -1.11022302462516E-16

 

[chiidzzz]

Forgot to say, I already tried it with and without parentheses , same error

 

[Joe4]

Incorrect, comparison operators are already treated last in the chain. The problem here is an IEE rounding error.

Right you are, my mistake. Blasted floating arithmetic error!
See: https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

The way to get around it is to round the result to 2 decimal places, i.e.

If Sheet1.Cells(i, 2).Value >= diff05 And Sheet1.Cells(i, 2).Value < Round(diff05 + 0.05, 2) Then

 

[chiidzzz]

Will give it a try, thank you

 

[chiidzzz]

Thank you jkpieterse and Joe4 , it worked.