0

I want to create either a macro or a UDF that can find cells in an excel worksheet that contains the following: POxxx PO xxxxxxx PO# xxxxx PO#xxxx (With x being numbers) The string could be at the start or the middle of cells. In addition, the function/macro should not find cells that contain entries like CORPORATE, where PO is part of a word.

All the cells that contains qualifying data, should be highlighted.

Community
  • 1
  • 1
  • What's stopping you from doing that exactly ? – Tim Williams Jan 15 '15 at 18:27
  • Tim, I have a problem to find a syntax that will find the sub-string anywhere in the cell content, especially at the start of a cell. –  Jan 19 '15 at 15:34
  • @TimWilliams, I am having problems finding a syntax that will find he sub-strings anywhere in the cell contents. I don't seem to find a syntax that will find the sub-strings if they are at the beginning of the cells. It also appears to be the case with the UDF that Gary's Student provided. –  Jan 19 '15 at 15:49

2 Answers2

0

This small UDF will return 1 is the match is present, otherwise 0

Public Function IsItThere(r As Range) As Long
    Dim st As String
    st = "0,1,2,3,4,5,6,7,8,9"
    ary = Split(st, ",")
    st = r.Text
    IsItThere = 1
    For Each a In ary
    If InStr(1, st, "PO" & a) > 1 Then Exit Function
    If InStr(1, st, "PO " & a) > 1 Then Exit Function
    If InStr(1, st, "PO#" & a) > 1 Then Exit Function
    If InStr(1, st, "PO# " & a) > 1 Then Exit Function
    Next a
    IsItThere = 0
End Function

You could also use Regular Expressions to find the pattern.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thanks for the response. Tim, to answer your question, I have trouble getting the syntax correct for finding the sub-string when it is at the beginning of the cell. It also appear to have the same problem –  Jan 19 '15 at 15:30
0

Try this:

Sub Tester()
    Dim c As Range
    For Each c In Selection.Cells
        c.Interior.Color = IIf(RegexpTest(c.Value), vbRed, vbGreen)
    Next c
End Sub


Function RegexpTest(v As String)
    Static re As Object 'note static: you must reset the VB environment
                        '  (press the "stop" button) if you edit the
                        '   Pattern below
    If re Is Nothing Then
        Set re = CreateObject("VBScript.RegExp")
        '"PO" then optional #, optional space, then 2-5 digits
        re.Pattern = "PO#?\s?\d{2,5}"
        re.ignorecase = True
    End If
    RegexpTest = re.test(v)
End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125