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!

 

[largeselection]

I thought perhaps I could use the select case like "*BOB*" or some such, but that doesn't seem to be working either.

 

[Richard Schollar]

You'd need to use the Like operator:

Select UCase(TextName)   'if case insensitivity required
Case Like "*BOB*"

But why do you want to use a UDF for this? I would have thought a worksheet formula would probably be faster

 

[largeselection]

I could do this with a lookup, I was just trying to add it so that I don't need to always open the reference sheet. I've added a couple of UDFs that use the Number to bring back the Name and they have been invaluable.

I think I'm doing something wrong with the construction. When I put it in, I get an error that says "was expecting case" when I put in ucase. Then when I put in Case Like, I get an error that says "was expecting >, <, =, ...".

 

[Richard Schollar]

Wow! Major brain fart on my part

You don't use Select Case like that. I think you could loop thru thru your words and use Instr to test eg:

Function Name(TextString As Text)
  Dim arr As Variant
  Dim i As Long
  arr = VBA.Array("Bob","Sue","Terry","Sam")
  For i = 0 To UCase(arr)
    If Instr(1,TextString,arr(i),vbBinaryCompare)>0 Then
       Name = "some value"
       Exit Function
    End If
  Next For
End Function

Make sense?

 

[largeselection]

Hmm yea I get that. I had something like this initially, but it took a decent amount of time to run the function through a long list. I guess I will just stick to needing a specific name construction since I think it might be too complex to try and use a construction that contains a part of a name.

So for now I will use:

Function number(name As String)
 
Select Case name
Case Is = "BOB"
number = "1"
Case Is = "Sam"
number = "2"
Case Is = "Harry"
number = "3"
.
.
.
etc
 
End Select
  
End Function

That will catch most of them until I can figure out a way to get it so that instead of having an exact match ("BOB") it could just contain it so it would work for ("BOB McGinty") as well while at the same time being efficient enough that I could use the function for 50k+ rows.

Thanks for your help!

 

[jputman]

Been a while since I worked with VB, but the 'Select Case True' construct is one of the few things I miss from VB when using C#. I believe that I remember being able to do it like this (gave a couple of examples):

Function number(name As String)
 
Select Case True
Case name.Contains("BOB")
       number = "1"
Case name.StartsWith("Sam")
       number = "2"
Case name = "Harry"
       number = "3"
.
.
.
etc
 
End Select
 
End Function

...that is untested, but I think it is correct. Good luck!

 

[largeselection]

Thanks for weighing in, unfortunately when I tried your code I got an "invalid qualifier" error.

Thanks again for helping!

 

[jputman]

Sorry... I was thinking about VB.Net... not VBA. Here you go:

Function testfunc(name As String) As String
    Select Case True
    Case name Like "*Bob*"
        testfunc = "1"
    Case name Like "Sam*"
        testfunc = "2"
    Case name = "Harry"
        testfunc = "3"
    End Select
    
End Function

 

[largeselection]

That worked very well as far as identifying when the name was in the middle of a string. Thanks!

I feel like I tried everything, I just didn't have the second As String at the start.

Is there any way to have it identify regardless of capitalization?

So it will work if I have:

Select Case True
Case name Like "*Bob*"
TestFunc = 1

and it will the number 1 in for anywhere where there is "Bob" in the string. So it can say "Bob Timmons" or "Timmons, Bob" and it will still identify it as "1" which is great!

But is it possible to take it a step further so that it will also identify "BOB Timmons" as "1"?

If not, I suppose it's not horrible I could just have more cases. so I could have Case name like "*BOB*" and case name like "*Bob*" and case name like "*bob*". Probably wouldn't have more caps than those scenarios since I likely would not have "BOb"