Text wildcards
 

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.

Text wildcards
 

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

--
Regards,
Tom Ogilvy

"Rbp9ad" wrote in message
...
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.