CODEWITHSUNDEEP
vbaexcel
orlin
orlin

Reputation: 19

VBA : variable with multiple value (text)

Really inexperienced in VBA I'm trying to built a macro searching differents strings in multiple columns. I'm searching pattern like : "[ang]" "[fre]" "_[ger]

For now, I'm able to find _[ang] in column H, but I think there's a way to find all three patterns with one formula. Important part is to add a note in a specific cell (that's why I use cel.Offset(0, 4))

Sub testsearch()
    For Each cel In ActiveSheet.Range("H1:H" & Range("H" & Rows.Count).End(xlUp).Row)
        If cel Like "*_[ang]*" Then
            cel.Offset(0, 4) = "_[ang]"
        End If
    Next cel
End Sub

Upvotes: 0

Views: 84

Answers (1)

warner_sc
warner_sc

Reputation: 848

What about this..

Sub testsearch()

    Application.ScreenUpdating = False

    Dim string_patterns As Variant

    string_patterns = Array("_[ang]", "[fre]", "_[ger]")

    For Each cel In ActiveSheet.Range("H1:H" & Range("H" & Rows.Count).End(xlUp).Row)

        For Each element In string_patterns

            If cel Like "*" & CStr(element) & "*" Then
                cel.Offset(0, 4) = CStr(element)
                Exit For
            End If

        Next element

    Next cel

    Application.ScreenUpdating = True

End Sub

Upvotes: 2

Related Questions