| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem With Select Case
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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. -- HTH Bob Phillips "Steve" wrote in message thlink.net... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bob and Bob
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" wrote in message thlink.net... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the
Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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 "Bob Kilmer" wrote in message ... 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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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" wrote in message ... 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 wrote: 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 "Bob Kilmer" wrote in message ... 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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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 "Steve" wrote in message hlink.net... 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 "Bob Kilmer" wrote in message ... 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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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 "Steve" wrote in message hlink.net... 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 "Bob Kilmer" wrote in message ... 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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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. -- Bob Kilmer "Thomas" wrote in message ... You're gonna have to set wksht somewhere,Where? Steve wrote: 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" wrote in message ... 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 wrote: 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 "Bob Kilmer" wrote in message ... 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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
Amen.
-- Bob Kilmer "Thomas" wrote in message ... Me thinks its something simple then,check the gas gauge first. Bob Kilmer wrote: 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. -- Bob Kilmer "Thomas" wrote in message ... You're gonna have to set wksht somewhere,Where? Steve wrote: 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" wrote in message ... 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 wrote: 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 "Bob Kilmer" wrote in message ... 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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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. -- Bob Kilmer "Steve" wrote in message hlink.net... <<Maybe something ...... has bad karma or something. :-} I never discount this as a possible cause because I have experienced it more than once! But not this time. FOUND THE PROBLEM!! Select Case Left(WkSht.Name, 3) Case "May" My worksheets are named MAY-03, JUN-03, JUL-03, etc. So Left(WkSht.Name, 3) and Left(ActiveSheet.Name, 3) were returning MAY, JUN, JUL, etc. For the first worksheet, Case "May" was case-sensitive. Case "May" was false while Case "MAY" was true. Same for the other worksheets. I changed all the Case statements in the macro to all caps and everything now works fine. Thanks for staying with me on this. I appreciate your help. Steve "Bob Kilmer" wrote in message ... 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 "Steve" wrote in message hlink.net... 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 "Bob Kilmer" wrote in message ... 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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
But Steve originally said:
What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve ------------------- Seems like you tried to eliminate case considerations early in the game. Regards, Tom Ogilvy "Bob Kilmer" wrote in message ... 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. -- Bob Kilmer "Steve" wrote in message hlink.net... <<Maybe something ...... has bad karma or something. :-} I never discount this as a possible cause because I have experienced it more than once! But not this time. FOUND THE PROBLEM!! Select Case Left(WkSht.Name, 3) Case "May" My worksheets are named MAY-03, JUN-03, JUL-03, etc. So Left(WkSht.Name, 3) and Left(ActiveSheet.Name, 3) were returning MAY, JUN, JUL, etc. For the first worksheet, Case "May" was case-sensitive. Case "May" was false while Case "MAY" was true. Same for the other worksheets. I changed all the Case statements in the macro to all caps and everything now works fine. Thanks for staying with me on this. I appreciate your help. Steve "Bob Kilmer" wrote in message ... 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 "Steve" wrote in message hlink.net... 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 "Bob Kilmer" wrote in message ... 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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem With Select Case
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. -- Bob Kilmer "Tom Ogilvy" wrote in message ... But Steve originally said: What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve ------------------- Seems like you tried to eliminate case considerations early in the game. Regards, Tom Ogilvy "Bob Kilmer" wrote in message ... 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. -- Bob Kilmer "Steve" wrote in message hlink.net... <<Maybe something ...... has bad karma or something. :-} I never discount this as a possible cause because I have experienced it more than once! But not this time. FOUND THE PROBLEM!! Select Case Left(WkSht.Name, 3) Case "May" My worksheets are named MAY-03, JUN-03, JUL-03, etc. So Left(WkSht.Name, 3) and Left(ActiveSheet.Name, 3) were returning MAY, JUN, JUL, etc. For the first worksheet, Case "May" was case-sensitive. Case "May" was false while Case "MAY" was true. Same for the other worksheets. I changed all the Case statements in the macro to all caps and everything now works fine. Thanks for staying with me on this. I appreciate your help. Steve "Bob Kilmer" wrote in message ... 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 "Steve" wrote in message hlink.net... 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 "Bob Kilmer" wrote in message ... 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 Kilmer "Steve" wrote in message hlink.net... What do you see if you execute MsgBox "'" & Left(WkSht.Name, 3) & "'" in the Case Else? "May" Steve "Bob Kilmer" wrote in message ... 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? -- Bob Kilmer "Steve" wrote in message thlink.net... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| select case in vba | Excel Discussion (Misc queries) | |||
| select case | Excel Discussion (Misc queries) | |||
| Select Case ignored | Excel Worksheet Functions | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| Select Case | Excel Discussion (Misc queries) | |||
![Steve[_27_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
