I have a macro that generates the string I'm using for a RegEx pattern and puts it in a cell named [RegExPattern1].
Sub StringStrings()
Dim i As Long, targ As Range, Ptrn As String
Set targ = [RegExPattern1]
targ.ClearContents
For i = 3 To [topRegEx].End(xlDown).Row
targ = targ & cells(i, [topRegEx].Column)
Next i
targ = "\d{1,3}[ /-]?(?!" & targ & ")[A-M]+"
End Sub
After running the macro I can replace the long and periodically growing pattern in my function with this:
.Pattern = [RegExPattern1]
However I would like to accomplish it within the function itself. Something like this:
Dim i As Long, Ptrn As String, MyPattern As String
For i = 3 To [topRegEx].End(xlDown).Row
Ptrn = Ptrn & cells(i, [topRegEx].Column)
Next i
MyPattern = "\d{1,3}[ /-]?(?!" & Ptrn & ")[A-M]+"
Not surprisingly, it doesn't work. How would I emulate what the middle 3 lines are doing in proper function code?