CODEWITHSUNDEEP
vba
Rwood
Rwood

Reputation: 65

Shorten a Like statement in VBA

Is there any way to shorten this Like statement in VBA

If ibasic Like "A1*" Or ibasic Like "A2*" Or ibasic Like "A3*" Or ibasic Like "A4*" Or ibasic Like "A5*" Or ibasic Like "A6*" Or ibasic Like "A7*" Or ibasic Like "A8*" Or ibasic Like "B1*" Or ibasic Like "B2*" Or ibasic Like "B3*" Or ibasic Like "B4*" Or ibasic Like "B5*" Or ibasic Like "B6*" Or ibasic Like "B7*" Or ibasic Like "B8*" Or ibasic Like "F1*" Or ibasic Like "F2*" Or ibasic Like "F3*" Or ibasic Like "F4*" Or ibasic Like "F5*" Or ibasic Like "F6*" Or ibasic Like "F7*" Or ibasic Like "F8*" Or ibasic Like "C1*" Or ibasic Like "S1*" Then

Upvotes: 4

Views: 103

Answers (4)

user4039065
user4039065

Reputation:

Try,

If CBool(InStr(1, "|A1|A2|A3|A4|A5|A6|", "|" & Left(ibasic, 2) & "|", vbTextCompare)) Then
    'true, do something ...
End If

Upvotes: 1

Erik A
Erik A

Reputation: 32682

You can combine all these comparisons in just two statements by making use of brackets, which match any one character inside them:

If ibasic Like "[ABF][1-8]*" OR ibasic Like "[CS]1*" Then

The dash expresses a range of characters here.

Upvotes: 8

Andy G
Andy G

Reputation: 19367

You can list the alternatives with a Select Case statement, having first extracted the left two characters:

data = "A100"

Select Case Left(data, 2)
    Case "A1", "A2": 'etc.
        Debug.Print True
    Case Else:
        Debug.Print False
End Select

Upvotes: 1

Vityata
Vityata

Reputation: 43593

Pass the option in an Array(), loop through them in a separate function and return the result:

Public Sub TestMe()

    Dim myInput As String: myInput = "A243"
    Dim myLikes As Variant

    myLikes = Array("A1*", "A2*", "A3*")
    Debug.Print CheckLikes(myLikes, myInput)

End Sub

Public Function CheckLikes(myLikes As Variant, myInput As String) As Boolean
    Dim myVars As Variant

    For Each myVars In myLikes
        If myInput Like myVars Then
            CheckLikes = True
            Exit For
        End If
    Next

End Function

Upvotes: 1

Related Questions