CODEWITHSUNDEEP
arraysexcelregexvbaexcel-formula
Waleed
Waleed

Reputation: 919

Using Regex formula (and inside array) to make it check for a pattern on three cells (using OR) on the same time

I have used this expression on the cell (K3) =RegexMatch(C3,"\b[Mm]od(?!erate).*\b[hH]\b").
and then later used it inside an array to evaluate the destination range respectively with the values of column (C).
I need to adapt the below code and formula to make it check three cells (using OR) on the same time.
I mean check the pattern on cells e.g ("C3" or "F3" or "G3") if match in any one of the cited cells then result of formula is true and vice versa.
I tried =RegexMatch((C3,F3,G3),"\b[Mm]od(?!erate).*\b[hH]\b") but it has no effect regarding cells (F3,G3).

Sub Regex_with_three_cells()

    Dim ws As Worksheet, lr As Long, x As Long, r_in As Variant, r_out()
    
     Set ws = ActiveSheet
     lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
     r_in = ws.Range("C3:C" & lr).Value
     
     ReDim r_out(1 To UBound(r_in), 1 To 1)
    
    For x = LBound(r_in) To UBound(r_in)
        r_out(x, 1) = RegexMatch(r_in(x, 1), "\b[Mm]od(?!erate).*\b[hH]\b")
    Next
    
    ws.Range("K3:K" & lr).Value = Application.Index(r_out, 0, 1)

End Sub

Public Function RegexMatch(str, pat) As Boolean
    Static RE As Object
     If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")
      RE.Pattern = pat
       RegexMatch = RE.Test(str)
End Function

Upvotes: 1

Views: 91

Answers (1)

FaneDuru
FaneDuru

Reputation: 42246

Please, try the next adapted solution:

Sub Regex_with_three_cells()

    Dim ws As Worksheet, lr As Long, x As Long, r_in As Variant, r_out()
    
     Set ws = ActiveSheet
     lr = ws.cells(ws.rows.count, 1).End(xlUp).Row
     r_in = ws.Range("C3:G" & lr).Value2
     
     ReDim r_out(1 To UBound(r_in), 1 To 1)
    
    For x = LBound(r_in) To UBound(r_in)
        If RegexMatch(r_in(x, 1)) Or RegexMatch(r_in(x, 4)) _
                                   Or RegexMatch(r_in(x, 5)) Then
            r_out(x, 1) = True
        End If
    Next
    
    ws.Range("K3:K" & lr).Value2 = r_out

End Sub

Public Function RegexMatch(str) As Boolean
    Static RE As Object
     If RE Is Nothing Then Set RE = CreateObject("vbscript.regexp")
      RE.Pattern = "\b[Mm]od(?!erate).*\b[hH]\b" 'place the pattern here to make 
                                                 'the call more compact
       RegexMatch = RE.Test(str)
End Function

If you also need to write False in case of no match, the code can be easily adapted using Else: r_out(x, 1) = False...

Edited:

To use a formula able to deal with values of the three columns (of course, on the same row), you should use the above function and the next formula:

=IF(OR(RegexMatch(C31),RegexMatch(F3),RegexMatch(G3)),TRUE,FALSE)

Each RegMatch function will be evaluated as Boolean return and IF will return accordingly...

It is also possible to make the function able to accept multiple cells (ranges) and and process each of them, returning True and exiting when a cell processing returns RE.Test(CStr(cell.value))) = True... Its call, as UDF, can be transformed in:

  =RegexMatch(C3,F3,G3)

Upvotes: 2

Related Questions