Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case question
in A1 i have the text, "this is a test" i try to use this select case statement var1 = "test" sStr = Range("A1").Value Select Case sStr Case sStr Like "*" & var1 & "*" 'do something here the first case statement is skipped but if i break and hover the mouse over it, says it equals true. if i go to the immediate window and type ?sStr Like "*" & var1 & "*" TRUE it returns true. so why does it skip to the next select case statement? if it's the only select case statement, it exits the select case without doing anything. -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case question
Basically I don't think you can do that. You need to do your test as part of
the Select Case statement, the individual Case statements deal with the results of that test. If you want to use LIKE along with the Select Case, use it like this: Select Case aStr Like "*" & var1 & "*" Case True MsgBox "Matched" Case Else MsgBox "no match" End Select But since there can only be two answers: True or False, you might be better off with If aStr Like "*" & var1 & "*" Then 'do stuff when it is LIKE (True) Else 'not like here if you need option End If "Gary Keramidas" wrote: in A1 i have the text, "this is a test" i try to use this select case statement var1 = "test" sStr = Range("A1").Value Select Case sStr Case sStr Like "*" & var1 & "*" 'do something here the first case statement is skipped but if i break and hover the mouse over it, says it equals true. if i go to the immediate window and type ?sStr Like "*" & var1 & "*" TRUE it returns true. so why does it skip to the next select case statement? if it's the only select case statement, it exits the select case without doing anything. -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case question
I thought about this some more, and wondered if maybe you weren't trying to
set up using Select Case because you may want to evaluate several possibilities. If that's the case (no pun intended) then consider using If...Then...ElseIf construct: If aStr Like "*test*" Then 'could have used "*" & var1 & "*" also ElseIf aStr Like "*goat*" Then 'different result action ElseIf aStr Like "*chicken*" Then 'continue with ElseIfs as needed Else ' no match to any previous tests End If "JLatham" wrote: Basically I don't think you can do that. You need to do your test as part of the Select Case statement, the individual Case statements deal with the results of that test. If you want to use LIKE along with the Select Case, use it like this: Select Case aStr Like "*" & var1 & "*" Case True MsgBox "Matched" Case Else MsgBox "no match" End Select But since there can only be two answers: True or False, you might be better off with If aStr Like "*" & var1 & "*" Then 'do stuff when it is LIKE (True) Else 'not like here if you need option End If "Gary Keramidas" wrote: in A1 i have the text, "this is a test" i try to use this select case statement var1 = "test" sStr = Range("A1").Value Select Case sStr Case sStr Like "*" & var1 & "*" 'do something here the first case statement is skipped but if i break and hover the mouse over it, says it equals true. if i go to the immediate window and type ?sStr Like "*" & var1 & "*" TRUE it returns true. so why does it skip to the next select case statement? if it's the only select case statement, it exits the select case without doing anything. -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case question
If Gary really wants to use Select Case, I believe he can use
Select Case TRUE if he has multiple var1 Like ****** conditions to test. However, I've read comments to the effect that this structure (Select Case TRUE) is quite inefficient compared to the If Then/Else construct you have already suggested. "JLatham" wrote: I thought about this some more, and wondered if maybe you weren't trying to set up using Select Case because you may want to evaluate several possibilities. If that's the case (no pun intended) then consider using If...Then...ElseIf construct: If aStr Like "*test*" Then 'could have used "*" & var1 & "*" also ElseIf aStr Like "*goat*" Then 'different result action ElseIf aStr Like "*chicken*" Then 'continue with ElseIfs as needed Else ' no match to any previous tests End If "JLatham" wrote: Basically I don't think you can do that. You need to do your test as part of the Select Case statement, the individual Case statements deal with the results of that test. If you want to use LIKE along with the Select Case, use it like this: Select Case aStr Like "*" & var1 & "*" Case True MsgBox "Matched" Case Else MsgBox "no match" End Select But since there can only be two answers: True or False, you might be better off with If aStr Like "*" & var1 & "*" Then 'do stuff when it is LIKE (True) Else 'not like here if you need option End If "Gary Keramidas" wrote: in A1 i have the text, "this is a test" i try to use this select case statement var1 = "test" sStr = Range("A1").Value Select Case sStr Case sStr Like "*" & var1 & "*" 'do something here the first case statement is skipped but if i break and hover the mouse over it, says it equals true. if i go to the immediate window and type ?sStr Like "*" & var1 & "*" TRUE it returns true. so why does it skip to the next select case statement? if it's the only select case statement, it exits the select case without doing anything. -- Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case question
thanks, already using if,then else. i was just testing select case and wondered
why it didn't work, even though it evaluates to true. -- Gary "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... I thought about this some more, and wondered if maybe you weren't trying to set up using Select Case because you may want to evaluate several possibilities. If that's the case (no pun intended) then consider using If...Then...ElseIf construct: If aStr Like "*test*" Then 'could have used "*" & var1 & "*" also ElseIf aStr Like "*goat*" Then 'different result action ElseIf aStr Like "*chicken*" Then 'continue with ElseIfs as needed Else ' no match to any previous tests End If "JLatham" wrote: Basically I don't think you can do that. You need to do your test as part of the Select Case statement, the individual Case statements deal with the results of that test. If you want to use LIKE along with the Select Case, use it like this: Select Case aStr Like "*" & var1 & "*" Case True MsgBox "Matched" Case Else MsgBox "no match" End Select But since there can only be two answers: True or False, you might be better off with If aStr Like "*" & var1 & "*" Then 'do stuff when it is LIKE (True) Else 'not like here if you need option End If "Gary Keramidas" wrote: in A1 i have the text, "this is a test" i try to use this select case statement var1 = "test" sStr = Range("A1").Value Select Case sStr Case sStr Like "*" & var1 & "*" 'do something here the first case statement is skipped but if i break and hover the mouse over it, says it equals true. if i go to the immediate window and type ?sStr Like "*" & var1 & "*" TRUE it returns true. so why does it skip to the next select case statement? if it's the only select case statement, it exits the select case without doing anything. -- Gary |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case question
The Select Case structure is not a direct equivalent of an If-Then block.
This line... Select Case sStr says to find a Case statement below which equals the contents of the sStr variable. Presumably, sStr holds a String value of some kind. This Case statement... Case sStr Like "*" & var1 & "*" is the same as this one (for your given conditions)... Case True Since the String contents of sStr is not equal to True, the code for this Case statement is not executed. Rick "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... in A1 i have the text, "this is a test" i try to use this select case statement var1 = "test" sStr = Range("A1").Value Select Case sStr Case sStr Like "*" & var1 & "*" 'do something here the first case statement is skipped but if i break and hover the mouse over it, says it equals true. if i go to the immediate window and type ?sStr Like "*" & var1 & "*" TRUE it returns true. so why does it skip to the next select case statement? if it's the only select case statement, it exits the select case without doing anything. -- Gary |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case question
The Select Case structure is not a direct equivalent of an If-Then block.
This line... Select Case sStr says to find a Case statement below which equals the contents of the sStr variable. Presumably, sStr holds a String value of some kind. This Case statement... Case sStr Like "*" & var1 & "*" is the same as this one (for your given conditions)... Case True Since the String contents of sStr is not equal to True, the code for this Case statement is not executed. Of course, from this, you can see that using this Select Case statement... Select Case True then the individual Case statements, taken together, will operate like an If-Then-Else block. Personally, I'd rather use the If-Then-Else block instead. Rick |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case question
thanks for the explanation. like i mentioned, i did use if, then, else, i just
experiment with different ideas to see how they perform. when the select case statement returned true, i asked why if didn't function and execute the subsequent statement. -- Gary "Rick Rothstein (MVP - VB)" wrote in message ... The Select Case structure is not a direct equivalent of an If-Then block. This line... Select Case sStr says to find a Case statement below which equals the contents of the sStr variable. Presumably, sStr holds a String value of some kind. This Case statement... Case sStr Like "*" & var1 & "*" is the same as this one (for your given conditions)... Case True Since the String contents of sStr is not equal to True, the code for this Case statement is not executed. Of course, from this, you can see that using this Select Case statement... Select Case True then the individual Case statements, taken together, will operate like an If-Then-Else block. Personally, I'd rather use the If-Then-Else block instead. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Select Case question | Excel Programming | |||
select case question | Excel Programming | |||
VBA select case question | Excel Discussion (Misc queries) | |||
Select Case Question | Excel Programming |