Strange Formula Problem

[jaysh]

It's me again. Just want to thank you guys again for being very helpful.

Have a weird problem.

If i'm in excel and do =sum(at:at) it comes out to a dollar amount of 42.99 (expected)

However, when i'm in VBA and doing:

Set freight = ws1.Range("AT:AT")

ws3.Range("B2") = Application.Sum(freight)

It comes pretty close to the number but instead it is something like 42.989932

The weird part is that there are no extra decimel places anywhere in column AT to give it these extra places. Any ideas?

 

[delaneyjm]

It's a floating point issue likely. All computer programs suffer from it. It stems from converting numbers from base 10 to binary. Sometimes it doesn't go as cleanly as humans interpret.

 

[jaysh]

So from what it sounds like, there is no fix for that? I mean the numbers are so close that I can't see it being a problem. However, I want to make sure that if I go into alot higher numbers, it wont be throwing it off by dollars.

Any help?

Thanks!

 

[delaneyjm]

Can you post some sample data and the full code that you used?

 

[jaysh]

Uploaded the example file to:

http://www.showmethebacon.com/test/Excel Help.xls

Code:
http://www.showmethebacon.com/test/script.txt

Then check out sheet3's numbers.

Thanks!

 

[delaneyjm]

Checked over your file. VBA is returning a different value (albeit the same when formatted) compared to doing AutoSum.

You could use some form of rounding method after doing the Application.Sum or instead of this code:

Set freight = ws1.Range("AT:AT")
ws3.Range("B2") = Application.Sum(freight)

you could run

Set freight = ws1.Range("AT:AT")
ws3.Range("B2").Formula = "=SUM(AT:AT)"

 

[jaysh]

Thank you much!!