VBA code to check is value from array in present in a cell?

Question

I need to loop though a cell range that contains one or several locale ISO codes in a CSV fashion e.g esES, frFR, itIT, etc.

If ANY of these values are contained within a cell, I select it and paste it to another workbook. The latter part I got covered, but I can't figure how to make the former part work. This is the code I'm working with at the moment:

OTHERS_V = "*arAR*|*bgBG*|*csCZ*|*daDK*"

For Each cell In Intersect(Sheets("Requests").Range("G:G"), Sheets("Requests").UsedRange)
        If cell.Value Like OTHERS_V Then [...]

I'm pretty new to VBA and I don't know much about Regex in this language but from my experience this should read something like:

(anything + "arAR" + anything) OR (anything + "bgBG" + anything) OR [...]

etc.

It doesn't seem to work though. How would you go about accomplishing what I'm after in this context?

Make OTHERS_V and array not a string and loop through the array and do your like test on each one. — Scott Craner
– Scott Craner, Commented Jul 22, 2016 at 14:11

Scott Craner · Accepted Answer · 2016-07-22 14:14:56Z

1

As per my comments, put OTHERS_V list in an array and loop testing each one:

Sub fooo()
Dim OTHERS_V()
Dim cell As Range
Dim i As Long

OTHERS_V = Array("*arAR*", "*bgBG*", "*csCZ*", "*daDK*")

For Each cell In Intersect(Sheets("Requests").Range("G:G"), Sheets("Requests").UsedRange)
    For i = LBound(OTHERS_V) To UBound(OTHERS_V)
        If cell.Value Like OTHERS_V(i) Then
            'do your stuff
            Exit For
        End If
    Next i
Next cell


End Sub

answered Jul 22, 2016 at 14:14

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

Collectives™ on Stack Overflow

VBA code to check is value from array in present in a cell?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related