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