Avatar of John Carney
John Carney
Flag for United States of America asked on

Trying to understand how to build a macro around a very simple RegEx pattern

I'm starting to understand how to construct a pattern in RegEx, but I can't seem to get the hang of getting a macro to actually do something with a pattern. Pls take a look at the attached workbook and let me know how to construct my macro to get the desired result.

Thanks,
John
Sub MatchPattern()
[Myrange].Offset(0, 2).ClearContents
Dim RegEx      As VBScript_RegExp_55.RegExp
Dim rng1 As Range, cel As Range
    Set RegEx = New VBScript_RegExp_55.RegExp
    Set rng1 = [Myrange]
    With RegEx
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "\d\s\w{1-3}"
    End With
    For Each cel In rng1
        cel.Offset(0, 2) = cel & " 999"
    Next
   Set rng1 = Nothing
    Set RegEx = Nothing
End Sub

Open in new window

VeryBasicPatternMatch.xls
Microsoft Excel

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Bill Prew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Bill Prew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
John Carney

ASKER
Thanks Bill. If you wouldn't mind taking a look at another pattern, it's so frustrating to think you're getting it but you can't even make the slightest improvisation.  :-)

If you have the time and inclination, please let me know what's wrong with my pattern for MatchPattern2.

Thanks,
John
VeryBasicPatternMatch2.xls
Bill Prew

Okay, for one thing you need to change to this:

        .Pattern = "^\w{4,5}\d$"

The reason being this.  With RegEx pattern matching, it will search the entire cell contents, looking for a match to the pattern ANYWHERE in the string.  So one of your tests was "applesauce3".  Your pattern was "\w{4,5}\d", so you were basically looking for a run of 4 to 5 characters of the class word (letters, numbers, and a few special characters).  So a match would be found at:

apple[sauce3]

where the bracketed section was a valid match.  But that's not really what you wanted.  By adding the ^ at the left you indicate that the matching has to start right at the letmost character of the cell value.  And adding the $ at the end indicates that the matching has to end exactly at the last character of the cell value.  In your case that's what you want, if there are extra characters around a valid match substring, you don't want to count that as a match.

In addition, you might want to think about if you really want to use \w for the first part of the expression.  That class includes numbers, so something like "cat44" is a valid match.  Maybe you only want characters in the first 4 to 5 characters?

~bp
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes