Text wildcards

[Rbp9ad]

I wrote the following function to return vendor numbers from the name of the
vendor and the account number. Here is what I have so far.

Option Explicit
Function Vendor(VR As Variant) As String

Application.Volatile True

Dim VenNam As String
Dim Acct As Variant
Dim Crit As Variant

VenNam = RRLookup(VR, 4)

Select Case VenNam
Case Is = "Acme"

Acct = Strings.Left(RRLookup(VR, 11), 12)

Select Case Acct
Case Is = "123456789", "12 3456", "987654", "123 456"
Vendor = "36359"
Case Is = "45678", "987 65"
Vendor = "2810"
Case Is = "246810", "1357", "12345 6", "6789 45","456 879"
Vendor = "2814"
Case Else

Crit = RRLookup(VR, 5)

If Crit = "111-111-2222" Then
Vendor = "39363"
If Crit = "78904561-23" Then
Vendor = "39775"
If Acct = "Semi-Annual " Then
Vendor = "42026"
Else
Vendor = "Not Found"
End If
End If
End If
End Select


End Select
End Function

RRLookup is a custom function that will return a text string. This works but
if the text string returned is only ACME but many times the string returned
"ACME of Southern Michigan" it does not evaluate. I tried changing the first
case under the select case vennam to:

Case Is = "ACME*"
and
Case Is = "ACME" & "*"

but this does not work. I am trying to make this a general function for all
of my vendors, so keying in on just ACME will not help all that much.

 

[Tom Ogilvy]

Assuming your case statement will handle other names besides ACME,

try something like this:

Sub ab()
sStr = "Acme of Southern Michigan"
Select Case UCase(sStr)
Case "ACME" To "ACMEZ"
MsgBox "It's ACME"
Case Else
MsgBox "Not Acme"
End Select

End Sub