| 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
|
|||
|
|||
|
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
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 |
|
#9
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 |
|
#10
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 |
| 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)
Hybrid Mode
