Testing for specific words and alphanumeric strings that meet certain criteria

In the code below, in addition to testing for the occurence of any of the words in the list "Keywords_PAX". I need to be able to test for the presence of any 2-4 character alphanumerics, where the numbers will range from 1 to 100, and the letters will range from A to K. Examples: 1D, 23ACDF, 52HK. How would I do that? Can it be added to this code or will I need to do it with a more elaborate code involving RegEx?

Thanks,
John
Sub FindPAX()
Dim cel As Range, x As Range
For Each cel In [Fault_FuncGrpsCnt]
Set x = cel.Offset(0, -5)
    If x.Worksheet.Evaluate("COUNT(MATCH(""*""&Keywords_PAX&""*""," & x.Address & ",0))") > 0 Then
    cel = "PAX"
    Else: End If
Next
End Sub

Open in new window

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.

Patrick MatthewsCommented:
John,

If you add this function:

Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' For more info, please see:
    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' Pos = 0                   : the last match
    ' Pos = -1                  : the last match
    ' Pos = -2                  : the 2nd to last match
    ' Pos = <negative integer>  : the Nth to last match
    ' If Pos is non-numeric, or if the absolute value of Pos is greater than the number of
    ' matches, the function returns an empty string.  If no match is found, the function returns
    ' an empty string.  (Earlier versions of this code used zero for the last match; this is
    ' retained for backward compatibility)
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' ReturnType indicates what information you want to return:
    ' ReturnType = 0            : the matched values
    ' ReturnType = 1            : the starting character positions for the matched values
    ' ReturnType = 2            : the lengths of the matched values
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    ' Note: RegExp counts the character positions for the Match.FirstIndex property as starting
    ' at zero.  Since VB6 and VBA has strings starting at position 1, I have added one to make
    ' the character positions conform to VBA/VB6 expectations
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer()
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Evaluate ReturnType
    
    If ReturnType < 0 Or ReturnType > 2 Then
        RegExpFind = ""
        Exit Function
    End If
    
    ' Create instance of RegExp object if needed, and set properties
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
        
    ' Test to see if there are any matches
    
    If RegX.Test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        
        Set TheMatches = RegX.Execute(LookIn)
        
        ' Test to see if Pos is negative, which indicates the user wants the Nth to last
        ' match.  If it is, then based on the number of matches convert Pos to a positive
        ' number, or zero for the last match
        
        If Not IsMissing(Pos) Then
            If Pos < 0 Then
                If Pos = -1 Then
                    Pos = 0
                Else
                    
                    ' If Abs(Pos) > number of matches, then the Nth to last match does not
                    ' exist.  Return a zero-length string
                    
                    If Abs(Pos) <= TheMatches.Count Then
                        Pos = TheMatches.Count + Pos + 1
                    Else
                        RegExpFind = ""
                        GoTo Cleanup
                    End If
                End If
            End If
        End If
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1)
            For Counter = 0 To UBound(Answer)
                Select Case ReturnType
                    Case 0: Answer(Counter) = TheMatches(Counter)
                    Case 1: Answer(Counter) = TheMatches(Counter).FirstIndex + 1
                    Case 2: Answer(Counter) = TheMatches(Counter).Length
                End Select
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        
        Else
            Select Case Pos
                Case 0                          ' Last match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(TheMatches.Count - 1)
                        Case 1: RegExpFind = TheMatches(TheMatches.Count - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(TheMatches.Count - 1).Length
                    End Select
                Case 1 To TheMatches.Count      ' Nth match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(Pos - 1)
                        Case 1: RegExpFind = TheMatches(Pos - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(Pos - 1).Length
                    End Select
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    
    Else
        RegExpFind = ""
    End If
    
Cleanup:
    ' Release object variables
    
    Set TheMatches = Nothing
    
End Function

Open in new window



then you can test for it thusly:


If RegExpFind(CStr(x.Value), "1[0-9]*[A-K]+", 1, False) <> "" Then
    ' match found
Else
    ' no match
End If

Open in new window



For more info, please see: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

Patrick
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
Patrick MatthewsCommented:
BTW, that False for the 4th argument makes it NOT case sensitive.  To make it case sensitive:

If RegExpFind(CStr(x.Value), "1[0-9]*[A-K]+", 1) <> "" Then
    ' match found
Else
    ' no match
End If

Open in new window

0
byundtCommented:
John,
You might try testing the first and last characters in x:
If IsNumeric(Left(x,1)) And UCase(Right(x,1))>="A" And UCase(Right(x,1))<="K" Then

Open in new window


Brad
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Patrick MatthewsCommented:
Brad,

You could, but then you would falsely validate a string such as 23AZK

:)

Patrick
0
byundtCommented:
Patrick,
Your pattern rejects 23AZK but accepts 13AZK. The rejection is because of the presence or absence of a 1. Adding a ? will fix the false rejection, but then it accepts 23AZK as well. The ultimate fix might be:
If RegExpFind(CStr(s), "^1?[0-9]*[A-K]+$", 1, False) <> "" Then

Brad

0
byundtCommented:
Here is a simple function that tests for a complete match of the pattern [number between 1 and 100] [one or more letters between A and K]
Function AltTest(s As String) As Boolean
Dim i As Long, jNum As Long, n As Long
Dim ss As String
n = Len(s)
jNum = Val(s)
ss = jNum & ""
If jNum > 0 And jNum <= 100 And n > Len(ss) Then
    For i = Len(ss) + 1 To n
        If Mid(s, i, 1) Like "[!A-K]" Then Exit Function
    Next
    AltTest = True
End If
End Function

Open in new window

0
Patrick MatthewsCommented:
Brad,

True.  My pattern was actually very sloppy: it actually did falsely validate 13AZK :(

This should fix it:

If RegExpFind(CStr(x.Value), "\b[1-9][0-9]*[A-K]+\b", 1) <> "" Then
    ' match found
Else
    ' no match
End If

Open in new window


:)

Patrick
0
Patrick MatthewsCommented:
Of course, if the input string happens to:

13AZK 13AK

it will still validate because 13AK is there.  If the idea is to validate the whole string fits the pattern:

If RegExpFind(CStr(x.Value), "^[1-9][0-9]*[A-K]+$", 1) <> "" Then
    ' match found
Else
    ' no match
End If

Open in new window


Patrick
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Patrick, your code works almost  perfectly, thanks.

Brad I love the ridiculous simplicity of yours but I may be using it incorrectly because it doesn't pick up on these records for example:
MNL / MNL 22H 21H 1SR / R   and    MNL/MNL 9K 7K 31A 32B 38J 36H 20A 24C 37D 51H 1SR / R
See how I'm using it below. I thought that perhaps  ~UCase(Right(x, 1)) >= "A"~  should be ~UCase(Right(x, 1)) >= 1~ but that didn't work either.
Any thoughts.

Thanks,
John
For Each cel In [Fault_FuncGrpsCnt]
cel.Select
Set x = cel.Offset(0, -5)
    If x.Worksheet.Evaluate("COUNT(MATCH(""*""&KWFuncGrp_PAX&""*""," & x.Address & ",0))") > 0 Then
    cel = "PAX"
    Else: End If
    
    If IsNumeric(Left(x, 1)) And UCase(Right(x, 1)) >= "A" And UCase(Right(x, 1)) <= "K" Then
    cel = "PAX"
    Else
    End If

Open in new window

0
byundtCommented:
John,
My one-liner is testing the entire contents of the cell. You could, however, use the same approach after splitting cell contents:

Dim v As variant
For Each v In Split(x)
     If IsNumeric(Left(v,1)) And UCase(Right(v,1))>="A" And UCase(Right(v,1))<="K" Then           'v is a row & seat combination
               cel = "PAX"
               Exit For
     End If
Next

Open in new window


If you already have Patrick's main RegExpFind function in your workbook, then adding the simple five lines of code to invoke it will give a more solid solution to your problem. I do suggest changing the pattern to:
If RegExpFind(CStr(x.Value), "\b1?[0-9]{0,2}[A-K]+\b", 1) <> "" Then cel = "PAX"

Open in new window


 Brad
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi Brad, I'm confused.  Can you put your code exactly where it goes in my code? Assuming it goes ...
Sub FindFuncGroup()
Dim cel As Range, x As Range
[Fault_FuncGrpsCnt].Select
For Each cel In [Fault_FuncGrpsCnt]
Set x = cel.Offset(0, -5)
If x.Worksheet.Evaluate("COUNT(MATCH(""*""&KWFuncGrp_PAX&""*""," & x.Address & ",0))") > 0 _
Or RegExpFind(CStr(x.Value), "1[0-9]*[A-K]+", 1, False) <> "" Then  
cel = "PAX"
Else: End If
Next
End Sub

Open in new window

0
byundtCommented:
Using my suggested pattern in Patrick's function, you might make the code:
Sub FindFuncGroup()
Dim cel As Range, x As Range
[Fault_FuncGrpsCnt].Select
For Each cel In [Fault_FuncGrpsCnt]
Set x = cel.Offset(0, -5)
If x.Worksheet.Evaluate("COUNT(MATCH(""*""&KWFuncGrp_PAX&""*""," & x.Address & ",0))") > 0 _
Or RegExpFind(CStr(x.Value), "\b1?[0-9]{0,2}[A-K]+\b", 1) <> "" Then  
     cel = "PAX"
End If
Next
End Sub

Open in new window

0
byundtCommented:
Using my code instead of Patrick's:
Sub FindFuncGroup()
Dim cel As Range, x As Range
Dim v As Variant
For Each cel In [Fault_FuncGrpsCnt]
    Set x = cel.Offset(0, -5)
    If x.Worksheet.Evaluate("COUNT(MATCH(""*""&KWFuncGrp_PAX&""*""," & x.Address & ",0))") > 0 Then cel = "PAX"
    For Each v In Split(x)
        If IsNumeric(Left(v, 1)) And UCase(Right(v, 1)) >= "A" And UCase(Right(v, 1)) <= "K" Then    'v is a row & seat combination
            cel = "PAX"
            Exit For
        End If
    Next
Next
End Sub

Open in new window


Brad
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Brad and Patrick, I've been swamped with other projects today. Pls forgive me if I can't can't back to this til next week.

- John
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
I think I've got this working properly. Thank you for all your patience and thought.

- 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.