Link to home
Create AccountLog in
Avatar of John Carney
John CarneyFlag 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
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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
Avatar of Bill Prew
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