Problem With Select Case

[Steve]

I have three worksheets named May-03, Jun-03 and Jul-03 in a workbook. I have
the following code in a macro:

Dim Wksht As Worksheet
Wksht.Activate
MsgBox Left(WkSht.Name, 3)
Select Case Left(WkSht.Name, 3)
Case "May"
RowRef = "467"
Case "Jun"
RowRef = "468"
Case "Jul"
RowRef = "469"
Case Else
MsgBox ..................
End Select

No matter which worksheet I run this on, I get the correct name of the worksheet
in MsgBox Left(WkSht.Name, 3) but then the Select Case always go to the Case
Else statement. If the Macro gets it right in the Magbox function, why doesn't
it get the Case statement?

Steve

 

[Bob Phillips]

Steve,

Where does the variable Wksht get set.? I did a simple test and your code
worked for me, so I would be interested in where it gets set.

 

[Bob Kilmer]

Dunno, Steve. Worked okay for me. How does Wksht come to reference a
worksheet? (I don't see a Set statement.)

What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the
Case Else?

 

[Steve]

Thanks to both of you for responding!

There's a For Each/Next loop that is part of the code:

For Each WkSht In ActiveWorkbook.Worksheets

Wksht.Activate
MsgBox Left(WkSht.Name, 3)
Select Case Left(WkSht.Name, 3)

<<Case Statements >>

Next Wksht

Steve

 

[Steve]

What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the
Case Else?

"May"

Steve

 

[Bob Kilmer]

MsgBox "'" & Left(WkSht.Name, 3) & "'" includes a single quote string at
either end of the Left function, so I would have expected 'May'. The single
quotes just bracket the ends of the string, that's all. It's something I
sometimes do to verify the extents of the string.

I can't imagine why the Select Case is not working for you. What do you get
if you substitute a string for Left()?

Select Case "May"
<<Case Statements >>
End Select

 

[Steve]

Bob,

I also tried the following:

If Left(WkSht.Name, 3) = "May" Then
MsgBox "Hello"
End If

and the message "Hello" does not come up.

For a moment I thought it might be a References problem but Left("Mayville",3)
worked.

Steve

 

[Thomas]

This is basically what you have,does it not run correctly for you
Dim Wksht As Worksheet
Set Wksht = ActiveSheet
Wksht.Activate
MsgBox Left(Wksht.Name, 3)
Select Case Left(Wksht.Name, 3)
Case "May"
MsgBox "My"
Case "Jun"
MsgBox "Jn"
Case "Jul"
MsgBox "Jy"
Case Else
MsgBox " .................."
End Select

 

[Steve]

Thomas,

Rather than Set Wksht, I have For Each Wksht and Next Wksht after End Select.

See my last two responses to Bob for what works and what doesn't.

Thanks!

Steve

 

[Thomas]

You're gonna have to set wksht somewhere,Where?

Thomas,

Rather than Set Wksht, I have For Each Wksht and Next Wksht after End Select.

See my last two responses to Bob for what works and what doesn't.

Thanks!

Steve

 

[Bob Kilmer]

How about

If LCase(Left(Trim(WkSht.Name), 3)) = "may" Then
MsgBox "Hello"
End If

or

Dim v as String
v = LCase(Left(Trim(WkSht.Name), 3))

Debug.Print v, Len(v)
Debug.Print v = "may"

If v = "may" Then
MsgBox "Hello"
End If

....

 

[Bob Kilmer]

If Left(WkSht.Name, 3) = "May" Then

MsgBox "Hello"
End If

and the message "Hello" does not come up.

You really need to determine what Left(WkSht.Name, 3) is returning. (Not
that you should have to go to this much trouble.)

Dim s As String, i As Integer
s = Left("May-03", 3)
For i = 1 To Len(s)
Debug.Print Mid(s, i, 1), Asc(Mid(s, i, 1)),
Select Case Mid(s, i, 1)
Case "M": Debug.Print "M matches"
Case "a": Debug.Print "a matches"
Case "y": Debug.Print "y matches"
Case Else
End Select
Next i
End Sub

Have you tried the code in a squeaky clean new workbook? Maybe something is
corrupt or has bad karma or something. :-}

 

[Bob Kilmer]

Thomas,
We questioned Steve on this earlier. I am assuming we are past that. He
reported that MsgBox Left(WkSht.Name, 3) yields what he expects (May, Jun,
Jul, etc.). He would be getting an error "Object variable or With block
variable not set" if WkSht were Nothing.

 

[Thomas]

Me thinks its something simple then,check the gas gauge first.

 

[Bob Kilmer]

Amen.

--
Bob Kilmer

Me thinks its something simple then,check the gas gauge first.

 

[Bob Kilmer]

Ah! So you lied to us at the very beginning when you said "I have three
worksheets named May-03, Jun-03 and Jul-03...". <bg> Yes. You must always
look out for case when comparing strings. Use If LCase("sTriNg) =
"string"... or If UCase(strVar1) = UCase(srtVar2)...., etc.

 

[Tom Ogilvy]

But Steve originally said:

What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in

the
Case Else?

"May"

Steve

 

[Bob Kilmer]

Yes. I tried to suggest that. Steve also said, in his first post that

I get the correct name of the worksheet in MsgBox Left(WkSht.Name, 3)

I thought he would have caught the case thing earlier, but ... hey,
whatever. I guess Steve learned something. I can't count how many times
folks have sworn something is "the same" as something else when, to a
computer, it is not.