CODEWITHSUNDEEP
excelvbastring-search
Marshall Gu
Marshall Gu

Reputation: 137

VBA String Search for Contains a Number

I'm doing a data quality check on a large column of strings (ie. Last Name) and want to see if they contain a number.

The VBA code that I've tried so far should be straight forward: if the LastName field contains 1 or 2 (etc.) than the ReasonCode = 3. Later on, if ReasonCode = 3, the results spits out "Contains a number"

However, in situations like 'marshall', it's still populating "Contains a number"

ElseIf LastName Like "*#*" Or FirstName Like "*#*" Then

ReasonCode = 3

End If

ElseIf ReasonCode = 3 Then

    Cells(RowT, 16).Value = "Contains a number"

ReasonCode = 0

End If

Upvotes: 0

Views: 1006

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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.

Upvotes: 2

Related Questions