VBA If statement not resolving correctly

[gers1978]

I have a function where a string and a string-based array are passed in.

Using the VBE Watch function I can see the string passed in is:

TD1a/b/ PMO & CT

The array contains:

TD1a/b/ PMO & CT
TD1a/b/ PMO & CT
""

The line:

If staffName = whichArray(intCount) Then

is resolving to False, in other words it's skipping to the Else part, even when intCount = 0 or 1.

However if I run it when the string and array contain "TD1" it works fine.

Seems like either the /, space or & are causing issues?

Also posted here: http://www.ozgrid.com/forum/showthread.php?t=200657&p=774921#post774921

 

[Eric W]

My first guess is that there's a space, or non-displayable character in one of your strings. Stop your macro at that comparison line, and check the length of each string. If they're not the same, then they won't match. If they are the same, then you may need to use Mid and check the strings byte by byte. Once you see what's happening, you can use some of the other string processing functions to massage your data so that you can compare them.

 

[gers1978]

My first guess is that there's a space, or non-displayable character in one of your strings. Stop your macro at that comparison line, and check the length of each string. If they're not the same, then they won't match. If they are the same, then you may need to use Mid and check the strings byte by byte. Once you see what's happening, you can use some of the other string processing functions to massage your data so that you can compare them.

You're right, I used the Immediate Window to check the length of the string vs length of array element and it was 16 vs 15, one had a space at the end!

 

[Eric W]

Glad you figured it out! Thanks for the feedback.