Can Wild Card Characters be used in VBA Select Case statements

[shade2189]

Dear Excel Community,

I have a file that has several products. It is a listing of Drug families with several similar names in each family. I would like to write a case statement that looks at the key name with Wild Card Characters.

For example here are some of the products in the Xenlymeth Family:

Xenlymeth 100g
Xenlymeth 200g
Xenlymeth carbonate 1.5g
Xenlymeth Sodium Cacitrate 2g

I would like to write a Case statement in VBA that is looking for "*Xenlymeth*" and would essentially select any item that meets this criteria. I tried to write the code, but it skipped right over the items I was looking for. I don't think I can use Wild Card characters but am not sure. Several of the product names are very involved even though there is some commonality between them. There are about 10 Product families I am looking at, but over 140 individual products. Ten (10) Case statements would make my life very easy.

Does any one know if you can use Wild card characters and what the Syntax would be?

Thank you very much.

Shade2189

 

[Eric W]

Select Case won't work, but you can do something like:

mytext = "bal"
    
    If mytext Like "va*" Then
        Debug.Print 1
    ElseIf mytext Like "ba*" Then
        Debug.Print 2
    Else
        Debug.Print 3
    End If

 

[humdingaling]

normal case wont work
you need to setup the case

Sub LikeCase()
    
    Dim rng As Range
    
    For Each rng In Range("A2:A25")
    
        Select Case True
            
        Case rng.Value Like "*Xenlymeth*"
            'insert code here
        
        Case rng.Value Like "*Hydro*"
            'insert code here

        Case Else
            'insert code here
            
        End Select
    
    Next
    
End Sub

 

[My Aswer Is This]

Would something like this work for you:
If InStr(Cells(i, 1).Value, "Xenlymeth") Then