RegEx that changes interior color of cells containing 1 or 2 digits followed by 1 to 8 letters

I haven't worked with RegEx for a couple of years and I've forgotten most of what I knew then. I have some recollection about patterns and I think my pattern might be  "\d{1-9}+[A-M]" or "[1-99]+[A-M]". Am I even close on either one of those?

Here's what I need:

1.

I want to look in this range: Range([U2], [U10000].End(xlUp)) for cells containing whatever the correct pattern is.

2.

I need everything else in the entire code.  :-)
Thanks,
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
Actually your RegEx would be:
[0-9]{1,2}[a-zA-Z]{1,8} would match 1 to 2 digits followed by 1 to 8 letters.  Regardless of case.  This could also be written as (?i)[0-9]{1,2}[a-z]{1,8}
[0-9]{1,2}[a-mA-M]{1,8} would match 1 to 2 digits followed by 1 to 8 letters A through M.  Again regardless of case.  This could also be written as (?i)[0-9]{1,2}[a-m]{1,8}

You can test these at http://regex101.com/.

-saige-
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, saige. Now I need everything that wraps around that. Starting with the Dim statements right through to "End Sub," that's how dense I have become!

In effect the code should say something like:

For each cell in Range([U2], [U10000].End(xlUp))
if the cell contains a match for the pattern Then
   cell.EntireRow.Interior.Color = RGB(180,137,116)
   cell.EntireRow.Hidden = True
End If
Next cell

The color is for filtering later in the project. Hopefully I'll be able to understand the syntax well enough to be off and running again!

Thanks in advance,

John
0
it_saigeDeveloperCommented:
With the following data set:Capture.JPGAnd the following method:
Private Sub FilterRange()
    Dim fPattern As String: fPattern = "(^[0-9]{1,2}[a-m]{1,8}$)"
    Dim fExpression As New RegExp
    Dim fInput As String
    Dim fRange As Range
    
    Set fRange = ActiveSheet.Range("B1:B20")
    
    For Each cell In fRange
        If fPattern <> "" Then
            fInput = cell.Value
            With fExpression
                .Global = True
                .MultiLine = False
                .IgnoreCase = True
                .Pattern = fPattern
            End With
            
            Set matches = fExpression.Execute(fInput)
            If matches.Count = 1 Then
                cell.EntireRow.Interior.Color = RGB(180, 137, 116)
            Else
                cell.EntireRow.Interior.Color = RGB(255, 255, 255)
            End If
        End If
    Next
End Sub

Open in new window

I get the following results:Capture.JPG-saige-
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi saige, I get a "User-define type not defined" error on "Dim fExpression As RegExp."  How do I fix that?
In some of my old RegEx routines I had to add this for it to work, so I added it but of course it still didn't work:
Dim MyDic
Set MyDic = CreateObject("scripting.dictionary")
Set RegEx = CreateObject("vbscript.regexp")

Open in new window

So, how do I get your code to work?

Thanks,
John
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
It just occurred to me to add "Microsoft VBScript Regular Expressions 5.5" to References and that gets me past my first stumbling block mentioned above. But then it bugs on ".Global" with this error: "Object variable or with block variable not set."

How do I get past that?

Thanks,
John
0
it_saigeDeveloperCommented:
You need to include a reference to 'Microsoft VBScript Regular Expression 5.5'.

1. Select the '[b]Developer[/b]' tab.

Capture.JPGI don't have a 'Developer' tab.  How do I get it?

2. Select the '[b]Visual Basic[/b]' icon from the '[b]Code[/b]' section of the '[b]Developer[/b]' ribbon.

Capture.JPG

3. Select '[b]Tools[/b]' --> '[b]References[/b]', from the top menu.

Capture.JPG

4. Locate and select '[b]Microsoft VBScript Regular Expression 5.5[/b]' the press '[b]OK[/b]'.

Capture.JPG-saige-
0
it_saigeDeveloperCommented:
Post the complete code that you are using.

-saige-
0
aikimarkCommented:
@it_sage

I recommend this version of your code:
Private Sub FilterRange()
    Dim fPattern As String
    Dim fExpression As New RegExp
    Dim fInput As String
    Dim fRange As Range

    fPattern = "^[0-9]{1,2}[a-m]{1,8}$"

    With fExpression
        .Global = False
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = fPattern
    End With

    Set fRange = ActiveSheet.Range("B1:B20")
    Application.Screenupdating = False

    For Each cell In fRange
            fInput = cell.Value
            
            If fExpression.Test(fInput) Then
                cell.EntireRow.Interior.Color = RGB(180, 137, 116)
            Else
                cell.EntireRow.Interior.Color = RGB(255, 255, 255)
            End If
    Next

    Application.Screenupdating = True
End Sub

Open in new window

for the following reasons:
* keep work outside of looping structures
* you can use the regexp object's Test() method instead of parsing the data with the Execute() method
* since you aren't capturing any data out of the string, there is no need for the grouping parentheses
* there is no need to check for fPattern being an empty string
* suspended screen updating while iterating.
0
it_saigeDeveloperCommented:
@aikimark - Agreed.  Good points and edit.

-saige-
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
saige and akimark, thank you, both versions work. However - my apologies - what I took for granted and forgot to mention is that I'm looking for my pattern in cells that contain other text. For example: "SEAT 37A VIDEO INOP. SEATS AFFECTED / IN FLIGHT."

How would I write the code to accommodate that?

Thanks,
John
0
aikimarkCommented:
Just remove the leading and trailing characters from the regex pattern:
    fPattern = "[0-9]{1,2}[a-m]{1,8}"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks to both of you. That last bit was crucial, akimark.

~ John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.