select case question

[Gary Keramidas]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Gary Keramidas]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Gary Keramidas]

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.