Number format Question?

[davisbrc]

I have a visual basic code that checks the excel spreadsheet to determine if the cells have the proper values. My spreadsheet is supposed to multiply a rate "X" times energy unit "Y" to produce a dollar value "Z". In the excel spreadsheet itself the numbers work out like they are supposed to. When I have visual basic check the math, visual basic truncates the rate "X" to a three (3) decimal number. I noticed when I have the rate "X" as a custom format in excel this happens, but when I change the cell format to General, the problem goes away.

The problem I am having is this:

This what the value should be
$0.09216 * 9800 = $903.168
but this is what VBA gives me
$0.0922 * 9800 = $903.56

I am new to coding VBA so be gentle because I know things can definately be improved on. I have also tried dimensioning my rates as double but it does not work.

Thanks in advance.


For Column = colstart To colstop
Do While numbldg <= numbldgtot
For bldg = bldgstart To bldgstop
If Column = kwtotcol Then
kwenergy1col = kwtotcol - 1
kwenergy2col = kwtotcol - 2
total = Worksheets("ECM Input Table").Cells(bldg, kwtotcol)
energy1 = Worksheets("ECM Input Table").Cells(bldg, kwenergy1col)
energy2 = Worksheets("ECM Input Table").Cells(bldg, kwenergy2col)
rate1 = Worksheets("ECM Input Table").Cells(raterow, kwenergy1col)
rate2 = Worksheets("ECM Input Table").Cells(raterow, kwenergy2col)
value1 = (rate1 * energy1) + (rate2 * energy2)

[Bob Phillips]

Post the workbook.

[davisbrc]

I posted a summary file of what I am trying to do in my original post.

Thanks

[Bob Phillips]

I don't get your point. There is nothing in the workbook showing the problem that you describe, and if I try to run the code it errors with a divide by zero error.

[davisbrc]

If you put a break in the code at Line 37 where is says "If Value1 = 0 then", you will see that rate1 and rate2 have a truncated value. The value of rate1 and rate2 is 0.0922 when you hover of the variable as you view the code. The value should be 0.09216 instead. My goal is to check the value in L9 that excel calculates versus what VBA calculates. If the values are different then the code is to produce an error and changed the fill color to red. The code is not anything complicated. I just dont understand why VBA is truncating rate1 and rate2.

With regards to the error message you are receiving, try opening up the VBA editor and running the code under Sheet1(ECM Input Table).

Hope this clears up some of the confusion.