BigBen pretty much answered your question on the usage of # with the Like operator, so I won't really go into that. But what I did notice is your usage of many Like operators in your If statement, so I figured I would take the opportunity to share a function I created a while ago and use frequently.
The purpose of this function is to reduce multiple Like statements when comparing against a single value. While this function doesn't address your specific issue, it may be helpful in the future.
Function OrLike(ByVal compareVar As Variant, ParamArray CompareArgs() As Variant) As Boolean
Dim i As Long
If IsArray(CompareArgs(0)) Then
For i = LBound(CompareArgs(0)) To UBound(CompareArgs(0))
If compareVar Like CompareArgs(0)(i) Then
OrLike = True
Exit Function
End If
Next
Else
For i = LBound(CompareArgs) To UBound(CompareArgs)
If compareVar Like CStr(CompareArgs(i)) Then
OrLike = True
Exit Function
End If
Next i
End If
End Function
First, the function checks the first value used in CompareArgs. If this value is an array, then it compares against the array, otherwise it will utilize the ParamArray keyword. This allows you to use this function in two ways:
With an Array variable as your arguments
Dim Arr() As Variant
Arr = Array("Blah*", "Blah2*")
If Orlike("BlahBlah", Arr) Then
' . . .
End If
Utilizing ParamArray
If OrLike("BlahBlah", "Blah*", "Blah2*") Then
' . . .
End If
Obviously, you don't want to use your current If statement that you provided. But if you were as an example, take a look how this function simplifies your statement and vastly improves readability by turning this:
ElseIf LastName Like "*1*" Or LastName Like "*2*" Or LastName Like "*3*" Or LastName Like "*4*" _
Or LastName Like "*5*" Or LastName Like "*6*" Or LastName Like "*7*" _
Or LastName Like "*8*" Or LastName Like "*9*" Or LastName Like "*0*" Then
Into this:
ElseIf OrLike(LastName, "*1*", "*2*", "*3*", "*4*", "*5*", "*6*", "*7*", "*8*", "*9*", "*0*") Then
Not only does it improve readability, but it may actually increase performance. The problem with VBA If...Then statements is that everything in the line must be evaluated, even after a statement that returns True.
This function takes all of these arguments, and evaluates each statement until one becomes True, then it immediately exits the function - ignoring the remaining arguments.
