Multiple Regex Patterns
 

Hi
How can I run 3 different regex patterns on 3 columns on the same wsheet?

I have already created one pattern to handle all 3 columns but it is
beginning to get quite messy and does not quite work for every situation at
the moment.
(^|[^-])[1-9]\d?(st|nd|rd|th)(?!-)|((^|\D)[1-9]\d?-[1-9]\d?(?=\D|$))|(^(\D+|\.)$)|([a-z]\s?)[1-9]\d?$|KS[1-9]\d?|Key\s+Stage\s+[1-9]\d?
It would seem easier to break that down to 3 separate patterns - but I
cannot make that work.

The scenario - from the main proc, files are selected from a folder and
proc2 called. Proc2 processes each file where the match is run.
As each file is processed, three Collections are used to store UNIQUE
matches from three target columns and results are later written to a log.

I have tried creating 3 objects in the main sub each with their own pattern
but that doesn't work correctly. It has to be more efficient to create the
RegExp object in the main sub rather than repeatedly create it when
processing each file in Proc2.

T.I.A.

Geoff

Referencing Microsoft VBScript Regular Expressions 5.5
Module level declaration:
Dim objRegExp As RegExp

Sub Main()
Set objRegExp = New RegExp
With objRegExp ???? 1 and 2 and 3
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = ???? 1 and 2 and 3
End With

For each file in folder
ProcessFile
Next

other stuff then€¦

If col1.Count 0 Then
rnum = .Cells(.Rows.Count, "P").End(xlUp).Row
For k = 1 To coL1.Count
.Cells(k + rnum, "P") = col1(k)
Next
End If

repeat for collection 2
repeat for collection 3
End Sub


Sub ProcessFile()
tbl2 = .Range(.Cells(2, 2), .Cells(finalrecords + 1, 6))
For k = LBound(tbl2, 2) To UBound(tbl2, 2)
For j = LBound(tbl2, 1) To UBound(tbl2, 1)
tbl2(j, k) = Application.Clean(Trim(tbl2(j, k)))
If Not Left(tbl2(j, k), 1) Like "*[!#[*,/]*" Then tbl2(j, k) = "."
If Not objRegExp.Test(tbl2(j, k)) Then '''<<<< currently
On Error Resume Next
Select Case k
Case 2
If objRegExp1.Test(tbl2(j, k)) Then ''' pattern1 ??
col1.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
only
End If
Case 3
If objRegExp2.Test(tbl2(j, k)) Then ''' pattern2 ??
col2.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
only
End If
Case 4
If objRegExp3.Test(tbl2(j, k)) Then ''' pattern3 ??
col3.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
only
End If
Case Else
End Select
On Error GoTo errHandler
End If '''<<<< currently
Next j
Next k
.Range(.Cells(2, 2), .Cells(finalrecords + 1, 6)) = tbl2
End Sub

Multiple Regex Patterns
 

Sorted..
Remove 'global' .Pattern from Main sub. Then in ProcessFile define separate
patterns in an If Else clause which is dependant on column k i.e If k = xx
Then objRegExp.Pattern = "xxx". Remove 'global' RegExp.Test and replace with
similar test for each k in the Select Case proc.

Geoff

"Geoff K" wrote:

Hi
How can I run 3 different regex patterns on 3 columns on the same wsheet?

I have already created one pattern to handle all 3 columns but it is
beginning to get quite messy and does not quite work for every situation at
the moment.
(^|[^-])[1-9]\d?(st|nd|rd|th)(?!-)|((^|\D)[1-9]\d?-[1-9]\d?(?=\D|$))|(^(\D+|\.)$)|([a-z]\s?)[1-9]\d?$|KS[1-9]\d?|Key\s+Stage\s+[1-9]\d?
It would seem easier to break that down to 3 separate patterns - but I
cannot make that work.

The scenario - from the main proc, files are selected from a folder and
proc2 called. Proc2 processes each file where the match is run.
As each file is processed, three Collections are used to store UNIQUE
matches from three target columns and results are later written to a log.

I have tried creating 3 objects in the main sub each with their own pattern
but that doesn't work correctly. It has to be more efficient to create the
RegExp object in the main sub rather than repeatedly create it when
processing each file in Proc2.

T.I.A.

Geoff

Referencing Microsoft VBScript Regular Expressions 5.5
Module level declaration:
Dim objRegExp As RegExp

Sub Main()
Set objRegExp = New RegExp
With objRegExp ???? 1 and 2 and 3
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = ???? 1 and 2 and 3
End With

For each file in folder
ProcessFile
Next

other stuff then€¦

If col1.Count 0 Then
rnum = .Cells(.Rows.Count, "P").End(xlUp).Row
For k = 1 To coL1.Count
.Cells(k + rnum, "P") = col1(k)
Next
End If

repeat for collection 2
repeat for collection 3
End Sub


Sub ProcessFile()
tbl2 = .Range(.Cells(2, 2), .Cells(finalrecords + 1, 6))
For k = LBound(tbl2, 2) To UBound(tbl2, 2)
For j = LBound(tbl2, 1) To UBound(tbl2, 1)
tbl2(j, k) = Application.Clean(Trim(tbl2(j, k)))
If Not Left(tbl2(j, k), 1) Like "*[!#[*,/]*" Then tbl2(j, k) = "."
If Not objRegExp.Test(tbl2(j, k)) Then '''<<<< currently
On Error Resume Next
Select Case k
Case 2
If objRegExp1.Test(tbl2(j, k)) Then ''' pattern1 ??
col1.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
only
End If
Case 3
If objRegExp2.Test(tbl2(j, k)) Then ''' pattern2 ??
col2.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
only
End If
Case 4
If objRegExp3.Test(tbl2(j, k)) Then ''' pattern3 ??
col3.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
only
End If
Case Else
End Select
On Error GoTo errHandler
End If '''<<<< currently
Next j
Next k
.Range(.Cells(2, 2), .Cells(finalrecords + 1, 6)) = tbl2
End Sub