CASE statements using wildcards

[Tom]

I am using the function below...

At this time I need to exactly specify each "case" in
order to pick up all matching records.

However, I would like to use a wildcard (either at the
beginning or end or both) in order to reduce the number of
case statements.

For instance, instead of having the statements...

Case "BM1": ConvertRank = "First Class"
Case "SK1": ConvertRank = "First Class"
Case "YN1": ConvertRank = "First Class"

I'd rather use...
Case "*1": ConvertRank = "First Class"


Any suggestions are greatly appreciated!!!

Thanks,
Tom


*********************************************



Public Function ConvertRank(BILLET As Variant, RANK As
Variant) As Variant

If BILLET = "SupportStaff" Then
Select Case RANK

Case "BM1": ConvertRank = "First Class"
Case "SK1": ConvertRank = "First Class"
Case "YN1": ConvertRank = "First Class"

Case Else: ConvertRank = "UNKNOWN"
End Select
End If

End Function

 

[Marshall Barton]

I am using the function below...

At this time I need to exactly specify each "case" in
order to pick up all matching records.

However, I would like to use a wildcard (either at the
beginning or end or both) in order to reduce the number of
case statements.

For instance, instead of having the statements...

Case "BM1": ConvertRank = "First Class"
Case "SK1": ConvertRank = "First Class"
Case "YN1": ConvertRank = "First Class"

I'd rather use...
Case "*1": ConvertRank = "First Class"
*********************************************

Public Function ConvertRank(BILLET As Variant, RANK As
Variant) As Variant

If BILLET = "SupportStaff" Then
Select Case RANK

Case "BM1": ConvertRank = "First Class"
Case "SK1": ConvertRank = "First Class"
Case "YN1": ConvertRank = "First Class"

Case Else: ConvertRank = "UNKNOWN"
End Select
End If

End Function

While you can not use the LIKE operator on Case statements,
it's not necessary to use separate cases:

Select Case RANK
Case "BM1", "SK1", "YN1" : ConvertRank = "First Class"
Case Else: ConvertRank = "UNKNOWN"
End Select

If that's all you have going on with the Select Case, then
it seems that a simple If might be easier:

If RANK Like "*1" Then
ConvertRank = "First Class"
Else
ConvertRank = "UNKNOWN"
End

OTOH, if you have several other numbers you want to check
for, maybe you could use something like:

Select Case Right(RANK, 1)
Case "1" : ConvertRank = "First Class"
Case "2" : ConvertRank = "Second Class"
Case Else: ConvertRank = "UNKNOWN"
End Select