Home > OS >  Using Regex formula (and inside array) to make it check for a pattern on three cells (using OR) on t
Using Regex formula (and inside array) to make it check for a pattern on three cells (using OR) on t

Time:01-27

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

CodePudding user response:

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...

  • Related