VBA Excel Testing for variance

[dprayner]

Hi people.

I am trying to test for a variance (subtotal1 - subtotal2 = variance). I am using If variance <> 0 Then MsgBox "Variance error" End If. This worked fine until I changed the formatting of the cell from currency to accounting. I would rather have accounting formatting if possible. Instead of the Variance cell having 0, now it has $ - . This doesn't seem to have the same value as 0. I need to know how to test for this NULL amount. Thank you. DAVE

 

[rmikesmith]

Dave,

You have something else going on here. Formatting does not change the underlying data representation. When I tested this, the code you showed worked fine, as expected, regardless of cell formatting. How are your variables declared and values assigned? What do the cell contents look like in the formula bar?


Regards,
Mike

 

[dprayner]

Hi Mike.

I am using a command button on an Excel spreadsheet to do the test. The variance field is E36. The formula for that cell is =E27-E35. E27 is a single amount and E35 is a subtotal of several amounts. Currently the E36 cell is formatted in Accounting format and displays $ - in the cell. When amounts are entered in E27 and E35, the E36 cell popualtes with $ - if there is no difference between E27 and E35 and $ (variance amount) if the don't match. The problem seem to occur when I attempt to test for the $ - as it doesn't equal 0. DAVE

 

[rmikesmith]

Dave,

I played this so the variance is computed in a cell and indeed, I see the $ - when the subtotals are equal (variance = 0). However, when I read this into a variable it equates to zero, as expected. Can you post the code you are using to read the variance and test the condition for MsgBox display?


Mike

 

[dprayner]

' Test for Problem Page and breakdown match.
If Range("E36").Text <> 0 Then
'PP Amount and Breakdown Amount Don't Match
MsgBox "Problem Page and breakdown amounts don't match.", vbExclamation, "Variance Error"
GoTo Redo
End If

Redo: Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[SkipVought]

Dave,

If you are doing some division (floating point arithmetic) you may get a small residual value that is not zero.

Use integer arithmetic.

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[dprayner]

Hi Skip. Thanks for the response. I believe that I have figured it out. If this had been calculated in the VBA and not in the spreadsheet I beleive you would be correct. I believe where I went wrong is that I used .Text instead of .Value. DAVE

 

[rmikesmith]

Dave,

Yes, that would be my take. .Text returns the contents as displayed not the underlying value.


Mike