I recently had a project where I had to total dollar figures in two columns and compare that total to the reported total in a third. Easy, right?

The VBA code was pretty simple:

If Cells(ActiveCell.Row, 4).Value + Cells(ActiveCell.Row, 6).Value <> _
     Cells(ActiveCell.Row, 8).Value Then
        Cells(ActiveCell.Row, 10).Value = “Totals don’t match”
        '(e.g. if D2 + F2 <> H2 Then J2 contains an error message)
End If

In a set of 70 rows, I kept getting two rows where the totals were equal, but reported as not equal.

I tested the total and match in a separate cell. Correct answer.

I tested the total and match in VBA and got the wrong answer.

I checked for stray decimals. I checked for numbers as strings. I checked for hidden values. I rounded each number in code. I found one column had Wrapped Text and fixed that. Still, all wrong answers.

I copied/pasted values only to remove any formatting issues. Wrong answer.

I deleted the numbers and inserted two other numbers and their total. Correct answer.

I retyped the actual numbers and ran the code. Wrong answer.

I found several articles about precision issues. (https://blogs.office.com/en-us/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/) but my numbers were way smaller. I remembered how you had to round numbers without a Round function: Multiply by 100 to turn into an integer, then divide by 100 to get the original value.

I changed the code to convert the numbers to long integers and tested the long integers. (They had to be Longs not Integers due to the 32,767 limit on Integer values.) Success.

Dim lngFirst, lngSecond, lngTotal as Long

lngFirst =  Cells(ActiveCell.Row, 4).Value * 100
lngSecond = Cells(ActiveCell.Row, 6).Value * 100
lngTotal = Cells(ActiveCell.Row, 8).Value * 100

If lngFirst + lngSecond <> lngTotal Then
   Cells(ActiveCell.Row, 10).Value = “Totals don’t match”
End If

If you want to try it, these numbers contain the floating point error. Note that this will not give an error in Excel, only in VBA code. The first row is the Column Letters.