Problem With Select Case

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
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.
 
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?
 
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
 
What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the
Case Else?

"May"

Steve
 
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
 
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
 
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
 
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
 
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
Click to expand...
 
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

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

and the message "Hello" does not come up.
Click to expand...

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. :-}
 
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.
 
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.
 
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)
Click to expand...

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.
 
Back
Top