troubleshooting Question

Converting macro code to function code

Avatar of John Carney
John CarneyFlag for United States of America asked on
Microsoft Excel
4 Comments2 Solutions233 ViewsLast Modified:
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?

Thanks,
John
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros