Code stepping in to elseif statement when elseif is definitely not true

I have a program that will highlight a cell if the value does not match the value on a corresponding sheet. It seems to work for the most part, but gives me false positives sometimes. It only gives me the false positive on a certain category it is checking (monetary value). Basically, there are many lines that have a formula at the end that verifies the validity of all categories matching. If any of the 5 categories do not match, the result will be “check,” as opposed to “valid” if they all do match. The program them goes down the column of Valid/Check and steps into another subroutine if the value is check. This will go category by category comparing the two values. It works perfectly to highlight any error, but it sometimes steps into an if statement when the monetary values match. I have attached code, and pictures that show the two values I am comparing are matching numbers, but for whatever reason it still steps in to my if comp1 does not equal comp 2 if statement.
This picture shows CORRECT detections of errors in column G, and false positives in column F.
false positives.png

(I felt obligated to block out descriptions so the money values don’t actually give away private information, but you can see the background highlighting that is happening). Again, any highlighting in column G is correct, any highlighting in F should not be there.

You can see here that when I step through the code it shows the two variables being equal, yet it still steps in to the “if they do not equal then do this” segment of the code. I can’t seem to figure out why it would do this for some and not others, as you can see in the first image, there are highlighted cells in column G that are not triggering the false positive highlight in column F, and some that do. It seems random and I cannot seem to figure out why it randomly wants to step in. Any ideas?

code.png

Please Login or Register  to view this content.

It will always highlight the ElseIf statement, because the procedure needs to evaluate it. But does it jump to the next line under it, or does it jump to End If?

it does indeed step in, as you can see, values are still the same when it does step in.


code2.png

A-ha!

Data type is different. monComp1 is Variant, monComp2 is Double.
Try declaring them like this:

Please Login or Register  to view this content.

Same goes for the other variables. If you don't type "as String" or "as Integer", etc. then the variables' data type will be Variant. I hope that solves it for you.

Originally Posted by RHCPgergo

A-ha!

Data type is different. monComp1 is Variant, monComp2 is Double.
Try declaring them like this:

Please Login or Register  to view this content.

Same goes for the other variables. If you don't type "as String" or "as Integer", etc. then the variables' data type will be Variant. I hope that solves it for you.

Changed my declarations to the following:

Please Login or Register  to view this content.

And unfortunately nothing has changed. (i didnt adjust the string and integer lines because they do not impact this part of the code).

Wow, such a letdown. I was so proud of myself...

I've run out of ideas now. Can you post a sample workbook that reproduces the false positives? Don't include confidential data. Maybe someone else can solve it without having a look at the workbook, but I'm afraid I can't do it. Maybe not even with a workbook. :D

unfortunately i cannot. i tried dumbing it down to only the portions that were involved (or so i thought) even with confidential information, the error went away. i have no idea why this would be happening.

this isnt something that hinders my code from working, i just wanted to optimize it and having a false positive doesnt ruin it....

thanks for your help, it is much appreciated. i guess this will be a mystery that wont be solved.

I managed to get a test workbook to duplicate the error without giving away sensitive information. I have uploaded to an external website. I tried to attach it on these forums but the 2.5 mb was apparently too large. I commented in the code where the error happens (sub routine highlightError).

To run this as i would, just click the 'validate' button which will active the sub 'run' and it goes from there through the rest of the code.

the error happens on row 13, journal line 12. The highlight in column G, test12345-fourteen dogs should be there, as its matching line on the Journal entry tab has an extra 's' at the end.

the highlight in column F should NOT happen (1,751.20 matches 1,751.20 on Journal entry tab).


any help would be appreciated.

You forgot to include the macro. (It's saved as .xlsx, not .xlsm.)
Also, you can upload it to this forum as well, you click "go advanced" instead of "post quick reply".