VBA : variable with multiple value (text)

Question

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

Like works for simple stuff. For anything non-trivial (like alternatives), look into regular expressions instead. — Mathieu Guindon
– Mathieu Guindon, Commented May 9, 2018 at 17:47

warner_sc · Accepted Answer · 2018-05-09 17:49:30Z

2

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

edited May 9, 2018 at 17:49

answered May 9, 2018 at 17:46

warner_sc

8486 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

You'll probably want to Exit For when a match is found.

Collectives™ on Stack Overflow

VBA : variable with multiple value (text)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related