G
Geoff K
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 2col1.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
onlycol2.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
onlycol3.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
onlyCase 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
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 2col1.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
onlycol2.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
onlycol3.Add tbl2(j, k), CStr(tbl2(j, k)) ''' unique match
onlyCase 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