Debug help - Select Case structure

[xenou]

Here's a bug that's got me crazy:

Note 1:
I tried changing the syntax to

Case "condition1", "condition2", "condition3"...

rather than

Case Is = "condition1","condition2", "condition3"...

This made no difference in the results shown in the immediate window.

Note2:
I've printed the condition with Debug.Print so I could see the value being tested and which block it runs through. Here's the immediate window:

Case 1: SENDER COMPANY NAME
Case 2: PICKUP DATE
Case 2: REFERENCE 1
Case 2: TRACKING NUMBER

As you can see, there are PICKUP DATE and TRACKING NUMBER are in the "case 2" block but they should have tested True for Case 1...


I've worked on this for 45 minutes...so any advice here would be much appreciated!!

'To hide or delete columns
For x = lCol To 1 Step -1
    
    strTest = CStr(Cells(ROW_WITH_HEADERS, x).Value)
 
    Select Case UCase(strTest)
        Case Is = "TRACKING NUMBER", "PICKUP DATE", _
            "SENDER COMPANY NAME", "SENDER ZIP", "RECEIVER CITY"
            Debug.Print "Case 1: " & UCase(strTest)
        Case Else
            If Arg1 = "2" Then Columns(x).Delete
            If Arg1 = "1" Then Columns(x).Hidden = True
            Debug.Print "Case 2: " & UCase(strTest)
        End Select

Next x

Thanks for reading and looking at this with me

EDIT: Note3 - I also moved the UCase function out of the block to just make strTest = UCase(CStr(Cells(ROW_WITH_HEADERS, x).Value)) - also didn't help...which I am kind of glad it didn't because I don't need something else that wouldn't make sense!!

 

[erik.van.geit]

Hi,

perhaps there are trailing spaces
a better test would be

Debug.Print "Case 1: " & UCase(strTest)  & "|"

result

Case 2: PICKUP DATE |

which should be

Case 2: PICKUP DATE|

see TRIM (& CLEAN) in the VBAhelpfiles

kind regards,
Erik

 

[galileogali]

Excuse Me. But It's work fine for me.

I try the two ways: with "Is=" and without "Is="

GalileoGali



Excel 2003, W XP Sp 2

 

[xenou]

Hi:
Thanks for the responses. I like the pipes to check for spaces, too.

Here's how things went:
I spent a good hour typing in values in the immediate window, mousing over variables, trying string comparisons, etc. etc. etc.

Take a look at the immediate window now:

1) I type in "PICKUP DATE" = "PICKUP DATE" (true)
2) I get the value of strTest (it is PICKUP DATE) (mouseover looks like "PICKUP DATE")
3) I substitute this value by copying it between the quotes on one side of the equal sign
4) Now "PICKUP DATE" (strTest) = "PICKUP DATE" (typed in) is false!

What I get...

?"PICKUP DATE" = "PICKUP DATE"
True
?strTest
PICKUP DATE
?"PICKUP DATE" = "PICKUP DATE"
False

I just don't see how it is possible to get this result under any circumstances:

?"PICKUP DATE" = "PICKUP DATE"
False

Nevertheless, this text came from this board when I copied in sample data from a post to answer question. Although letter for letter, the text is exactly the same, when I retyped the headers into the cells on the worksheet, the code works as expected.

I will have to assume that on the level of 1's and 0's the text that came from an HTML formatted web posting was somehow different from the same text typed into Excel -- the interesting thing is that even in the immediate window it "looks and smells" the same, but just...isn't. I even tried writing a cell value as a string ("PICKUP DATE") ... still fails the test of = "PICKUP DATE"!! Only by re-typing in native Excel could I get a good read at last.

I'll chalk this up to experience. At least I believe I can still expect my programs to work in most environments I am used to - maybe I just picked up some wierd, invisible tag that adheres to the cell.


Regards.

 

[Norie]

Alexander

One test you might want to try is the length of the string.

If you've copied this from a web page there's every possibility that there's some sort of invisible character in there.

One of the favourites, and I don't know why, is Chr(160).

You could also test for that using InStr.

 

[xenou]

Hi Norie...

Yes, Chr(160) was in the string (position 7).

I must say I feel relieved to have some closure on this!
Thanks!