Value not equaling what it should -- can't figure out why

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
275
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
I have a variable of type Double that is not always the value that it should be, and I can't figure out why. Here's some of the code.

Code:
TotalOnForm = Val(Replace(.Range("AC41").Value, ",", ""))

TotalNotAssigned = TotalOnForm - TotalsInArray

(There is other code that assigns the value to TotalsInArray, but it's complicated and I don't think it's the problem, so I did not include it here.)

TotalNotAssigned, TotalOnForm, and TotalsInArray are all of type Double. (They are meant to hold dollar values with two decimals.)

I added those three variables to the watch window so I could see what they were while I was stepping through my code.

Sometimes TotalNotAssigned is not what it should be. For instance, in the test I was just doing, TotalOnForm and TotalsInArray were both equal to the same thing: 237.11

However, TotalNotAssigned does not equal zero when that last line of code is run. Instead, it is equal to 2.8421709430404E-14. I do not understand why this is happening. Any ideas?

Here's a screenshot of the Watch window, so you can see what I mean.
https://postimg.cc/BLYFwPn9
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Upvote 0
Thanks very much to both of you for the information.

The first thing I decided to try was to add ROUND to one of the lines of code, and now it works.

Instead of

Code:
TotalNotAssigned = TotalOnForm - TotalsInArray

I now have

Code:
TotalNotAssigned = Round(TotalOnForm, 2) - Round(TotalsInArray, 2)

TotalNotAssigned now calculates the way it should. Thanks for pointing me in the right direction!
 
Last edited:
Upvote 0
Upvote 0
This is great to know about -- thank you! I will use the Worksheet Round function instead of the VBA Round function.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top