VBA code: MsgBox if a range of cells do not sum to 100%

[j.farr3ll]

Hi all,

I have the following code to display a message box if a range doesn't sum to 100%

Please Login or Register  to view this content.

However the msgbox appears even if the cells do sum to 100. I have tested all the ranges using the following code:

Please Login or Register  to view this content.

and found that the problem is in the range D6:D9. This range returns true as expected in most cases, but occasionally false even if the numbers sum to 100%.

i.e. 5% 5%
65% 41%
13% 43%
17% sums to 100% and returns true 11% sums to 100% but returns false.

Could anyone provide any ideas as to why this might be happening?

Thanks in advance,
James

[Domski]

Please edit your post to include code tags around your code.

Dom

[tarquinious]

Right now I can tell you why this is happening, but haven't figured out the solution just yet.

It is to do with how Excel stores the numbers when you reference them. By default Excel is reading the values as Integers, so the percentages are somehow being stored differently and the values 0.05, 0.41, 0.43 and 0.11 actually add up to 0.89.

The way I could tell this is by using the following code and changing the variable type from As Integer to Double, Single, Variant, etc and seeing what the result is.

I need to perform a bit more research into the correct way to store and read integers (as obviously the Integer variable type doesn't perform very well) to see how to fix this for you.

Please Login or Register  to view this content.

[Domski]

I think something like this should fix it:

Please Login or Register  to view this content.

Dom

[j.farr3ll]

Thanks for your help on this guys, understanding the integers gives me at least an idea of why this is happening.

Dom the code you post above solves the issue. Am I right in thinking this just makes excel round to 2 decimal places?

Cheers,
James