Excel not recognizing values as equal

Hi

I have a spreadsheet which is exported by a vendor's software. This spreadsheet has a column of numbers, but I can't consistently work with them in vba. It seems like some of them are not recognized as numbers.

As an example, I've stripped the sheet of all data except the column of numbers. Next I've set up a simple formula. Please see if you can make sense of why the formula returns "Not Equal". Changing the cell format does not help, and I also find it odd that if I set the cell format to number, changing the number of decimal places displayed does not effect what is shown in the cell.

Thanks

your numbers are string.

try =IF(H6=F11*1,"Equal","Not Equal")

Does that help?

The values are text, not numbers. Here's an easy way to fix that.

1) Put the number 1 in an empty cell
2) Copy that cell
3) Highlight all of the numbers to convert
4) Select Edit > Paste Special > Multiply > OK
5) Delete the cell you added, the cells have been converted to numbers

you might be able to change the import process so that they come in as numbers in the first place. why not attach a sample for us to look at.

Ok, that makes sense, thank you all for the explanation.

Now, what is the best way to convert in vba? I tried using cdbl() but it did not work.

range("A1")=range("A1") * 1

Thank you all. I have tried multiplying by 1 in my code, converting to number with the excel error checker, and converting to number by pastespecial multiplication, but the code I'm using still does not seem to recognize the values as the same. Unfortunately, do to personal data, I can't post my entire workbook, but here is a better version with my full code. Please take a look.

The code attempts to compile two sheets into a third while putting multiple entries in sheet 2 as a comment in the Result sheet. I have set up a msgbox to inform the user if the amount in sheet one does not equal all corresponding amounts in sheet 2. Only amounts where "Equation Result" are included. It helps to have Result tab activated while running procedure, but not necessary.

Thank you for your time

try this

Please Login or Register  to view this content.

Hello Dave,

I changed the macro to automatically change a value to double if it is a string and convert a number value to double. The problem is due to roundoff errors. I round the results to 5 digits. When I run the macro, I only get 2 loans that don not match up: 12655577 & 12690381. You should check the results yourself. Here is the modified macro. It has already added to the attached workbook.

Please Login or Register  to view this content.

tony h:

Did you try that, then run my "Compile" procedure? Because I just did and it did not work. The cells appear to have been changed to numbers, but my compile macro does not find the values equal, even though they clearly are when you step through the code.

I'll have a look at that Leith, thanks

Hi Leith

That works great.
Can you explain why roundoff errors were occurring when the numbers shown as a string all had 0 as the last digit shown? That doesn't make sense to me.
Also, why did you need to use the Val() function instead of just being able to use cdbl()?

Thanks again.

Hello Dave,

Because the numbers are double, the internal precision goes beyond the 15 digits Excel uses. VBA recognizes that minute difference and the test result is not equal.

The Val statement converts a text string, if possible, into a double value. I use the VarType statement to identify if the cell value is text (value of 8) and than use Val to convert it. Otherwise I assume it is a valid number and use CDbl to make sure the number is a double value.

ummmm...

I see that my post is a bit late since the thread has evolved with Leith on the scene, but anyway, here's my alternative for changing the format of the initial list.

Please Login or Register  to view this content.

Rob

But since the number (as a string) was -0.8500, wouldn't cdbl() change it to -.85? Are you saying it doesn't change it to EXACTLY that? Like it changes it to -.8500000000000000012?
I don't see where the minute difference is coming from.

It wasn't failing because it was still being evaluated as a string, was it?

Hello Dave,

If you go down to your IF statement that compares dblCumAdj and dblAdj, remove the rounding statement from each, and add a line that subtracts the two values you will see that that the result is not zero due to conversion of binary values to decimal values. If any of the decimals do not match then result is not equal.

hi Dave,

I don't have the understanding of Leith but I've read about floating point accuracy on a few sites which show some crazy examples. Check these out:
http://blogs.msdn.com/excel/archive/...ue-update.aspx
Chip's page is old, but I think the content is still correct for the latest version of Excel: http://www.cpearson.com/excel/rounding.htm
http://www.thecodecage.com/forumz/ex...nt-errors.html

Rob

Leith and Boro, thank you very much for the explanation. I was not aware this issue existed with IEEE. Chip's page was most helpful, particularly the section on storing fractions in binary.

I was even able to show the trick in 07 where the formula incorrectly shows 100,000 to some coworkers, and we all got a kick out of it.

Thanks again

LOL!

Good as gold - thanks for the feedback, the rep', and marking the post as solved

Rob