VB Number Matching Help Required

[hamhains]

Hi all,

I have run into an odd error in a program I'm writing, and have isolated the problem with a smaller sub-code. Basically my code checks to see if the first digit in an integer matches a whole number in another cell. For the most part the code works fine, but in some situations it fails to recognize matches and I can't figure out why. The following is what my sheet looks like, as well as the code I wrote to check this error.

Col.A Col.B
R1 2.11 2
R2 2.21 2
R3 2.32 2
R4 2.12 2
R5 2.22 2
R6 2.32 2

Please Login or Register  to view this content.

I don't know if there is a basic solution for this, but I'm stumped, and any help would be greatly appreciated.

[Ron Coderre]

I *think* you need something like this....

Please Login or Register  to view this content.

Does that help?

[hamhains]

Ron,

Thanks for the reply. That didn't seem to help, it actually made the code return values of "no" for every instance now.

Do you have any idea why the code I wrote is working for some digit combinations and not others? To me it seems arbitrary although it is probably a function of how excel is actually treating the variables.

[Ron Coderre]

Try running the macro in the attached workbook and let me know if I made any bad assumptions.

I get these results:

Please Login or Register  to view this content.

[hamhains]

Ron,

I think I mis-stated my question. The code I had was meant to match the 2 before the decimal place. I'm trying to have my program recognize only the whole portion of each entry. In the workbook I posted above, ideally, every cell in column C would result in a "yes".

I hope this helps

[Ron Coderre]

OK...thanks for the explanation.

Try this:

Please Login or Register  to view this content.

Does that work for you?

[hamhains]

Thanks Ron,

The code still doesn't work as it should. The output I get is "yes, yes, no, no, no, no". I'm not sure if this an artifact of excel numerical methods, as when I step through the code it literally reads IF (2) = (2), but treats it as false.

[Ron Coderre]

Perhaps if you post a sample workbook that demonstrates the issue, we'll be able to figure out the cause.

[hamhains]

Ron,

Please see attached.

[Ron Coderre]

OK...I'm very puzzled.

I run the macro I posted...and get incorrect results for rows 3 through 6.
Yet...If I replace the 2's in B3:B6 with 2's (Yes, I typed the same number over them), the resulting values are correct!

In fact, if I select B1:B6 then Data.Text-to-Columns...Finish
the resulting values are also correct.

I have no idea why or how that would happen. Did you type in those Col_B values or were they pasted in from another application?

[hamhains]

Hmm, that is weird. I did the same and it worked. I wrote the sheet on my own for comparison purposes, and did no copy or pasteing. No idea, but thanks!

[Ron Coderre]

This code seems to work...even on the original data...

Please Login or Register  to view this content.