UDF if formula that contains specific word

[largeselection]

I have a UDF which basically accomplishes a lookup. To do that I use this code:

Function name(number As String)
Select Case number
Case Is = "1"
name = "BOB"
.
.
.

there are a bunch of cases as well.

I'm not too familiar with UDFs so I am trying to figure out how to have one that does the same thing, but if it contains a certain word. Is it possible to do this with a case format since I have found that to be the fastest (computer processing speed)? Something like...

Function Number (Textname as string)
Select Case Textname
Case Contains = "BOB"
or 
Case is = "*BOB*"

I could probably do this with a long list of if statements, but I think that would run too slowly if I had to use it for a sheet with a lot of rows (50k+).

Thanks for any insight!

 

[jputman]

Just make the name lower (or upper) case before doing the checks, then make your check string the same way:

Function testfunc(name As String) As String
    Dim checkName As String
 
    checkname = LCase(name)        ' The name goes to lower

    Select Case True
    Case checkname Like "*bob*"     ' All match strings go to lower
        testfunc = "1"
    Case checkname Like "sam*"
        testfunc = "2"
    Case checkname = "harry"
        testfunc = "3"
    End Select
    
End Function

HTH

 

[largeselection]

GENIUS!

Hah, Thanks this is perfect. I love learning new things in excel.