Totals should be equal but Excel says they are not

[mcomp72]

In a workbook I am creating, I check to see if the user input all of the information they were supposed to. One way it does this is the look at the value in a cell that has a SUM formula in it, and compares it to some other cells where the user broke down each individual amount. (The amounts they are entering are currency amounts, so they always has two decimal places.) If the user did everything correctly, the two numbers that are being compared will be equal, and everything should work perfectly.

However, in my testing of the workbook, I ran across a scenario where the two numbers are identical, but Excel is saying they are not. My code:

Dim SummaryTotal As Double
Dim SumOfIndColTotals As Double

With ThisWorkbook.Sheets("Sheet1")

    SummaryTotal = .Range("G211").Value
    SumOfIndColTotals = Application.WorksheetFunction.Sum(.Range("I211:AL211"))

End With

Cell G211 has a SUM function in it, adding up cells G10:G210.
The cells being added up by the WorksheetFunction.Sum line all have SUM functions in them like G211, except for their individual columns. (I211 is a sum of I10:I210, etc.)

The very next line of code is where I'm having the issue. It's the beginning of an IF block.

If SummaryTotal > SumOfIndColTotals Then

When I step through the code and watch both of these variables, Excel says that both are equal to 97.27. Therefore, this IF block should be skipped over when I step through the code on this line. But it's not. The IF block executes, and gives a MsgBox warning the user there is a problem. Obviously, it should not do this, because the numbers ARE the same.

Here's a screenshot of my watch window.

If I change one of the values on my worksheet so that both of the above variables equal 177.27, the code works the way it should. If I change one of the values so that both of the above variables equal 97.28, the code works the way it should. However, if I change them so they are both equal to 107.27, the same problem happens.

Anyone have any ideas why this would be happening?

 

[Michael M]

Could be rounding in the individual items.
Try increasing the decimal places in the cells to see if there is a variance !

 

[mcomp72]

I just added two more decimal places to all the cells, but in every case, the extra decimal places just showed zeros. The problem still happened.

 

[mcomp72]

I figured out a work-around. I added 'Round' to the two lines of code that assign the totals to the variables, like so:

    SummaryTotal = Round(.Range("G211").Value, 2)
    SumOfIndColTotals = Round(Application.WorksheetFunction.Sum(.Range("I211:AL211")), 2)

I still don't understand why the heck it was happening, but at least it's solved now.

 

[Michael M]

you could also check to make sure one of cells isn't formatted as text !!

 

[Fluff]

I just added two more decimal places to all the cells, but in every case, the extra decimal places just showed zeros. The problem still happened.

Try showing 15 decimals