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
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 SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
.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
ASKER
If you have the time and inclination, please let me know what's wrong with my pattern for MatchPattern2.
Thanks,
John
VeryBasicPatternMatch2.xls