Finding specific number inside a string?

Question

This is a sample of my data, where name is in cell A1. The column C is not part of the data, it is there just to illustrate what is wanted.

name    cod     should be detected?
aa              no  
aa      14;15   no
aa      1;13;7  yes 
bb      8;9;1   yes 
bb      1;17    yes 
bb      11;21   no
cz      7;8     no  
cz      7;21    no
cz      8;1;20  yes 
db      1       yes 
db      13;1    yes 

I am trying to write a macro to detect on the column cod where the number 1 appears. For example, I don't want to find 10, 13, 21, but 1. The filled in numbers on this column go from 1 to 21.

All cod values are strings, but I want to find where there is 1, even if it appears mixed with other numbers in the string. Numbers in this column are always separated with ; without white spaces in between.

The following code will produce false positives:

Dim N As Range
Dim msg As String

Sub cod1()

msg = ""

For Each N In Range("A2", Range("A2").End(xlDown))
    If InStr(1, N.Offset(, 1), 1, vbTextCompare) > 0 Then
                msg = msg & "Code 1 was not supposed to be in Cod column." & vbLf
            Exit For
    End If
Next N

    If Len(msg) > 1 Then
        MsgBox msg
    Else: MsgBox "There are no code 1 values in Cod column."
    End If

End Sub

See results:

name    cod     should be detected? problem
aa              no  
aa      14;15   no                  false positive
aa      1;13;7  yes 
bb      8;9;1   yes 
bb      1;17    yes 
bb      11;21   no                  false positive
cz      7;8     no  
cz      7;21    no                  false positive
cz      8;1;20  yes 
db      1       yes 
db      13;1    yes 

The following code will produce false negatives:

Dim N As Range
Dim msg As String

Sub cod2()

msg = ""

For Each N In Range("A2", Range("A2").End(xlDown))
    If InStr(1, N.Offset(, 1), 1, vbTextCompare) > 0 And _
        InStr(1, N.Offset(, 1), 10, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 11, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 12, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 13, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 14, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 15, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 16, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 17, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 18, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 19, vbTextCompare) = 0 And _
        InStr(1, N.Offset(, 1), 21, vbTextCompare) = 0 Then
            msg = msg & "Code 1 was not supposed to be in Cod column." & vbLf
        Exit For
    End If
Next N

    If Len(msg) > 1 Then
        MsgBox msg
    Else: MsgBox "There are no code 1 values in Cod column."
    End If

End Sub

See results:

name    cod     should be detected? problem
aa              no  
aa      14;15   no  
aa      1;13;7  yes                 false negative
bb      8;9;1   yes 
bb      1;17    yes                 false negative
bb      11;21   no  
cz      7;8;10  no  
cz      7;21    no  
cz      8;1;20  yes                 false negative
db      1       yes 
db      13;1    yes                 false negative

So, how could one make the message box* appear only when a number 1 is detected inside the string?

*Code 1 was not supposed to be in Cod column.

---

_{Looking for a solution that works with Excel 2007 and newer versions.}

Answer 1

You can use the Like operator to find the characters:

Dim N As Range
Dim msg As String

Sub cod1()

    Dim expression As String
    msg = ""

    For Each N In Range("A2", Range("A2").End(xlDown))
        expression = ";" & N.Offset(, 1) & ";"
        If expression Like "*;1;*" Then
           msg = msg & "Code 1 was not supposed to be in Cod column." & vbLf
        End If
    Next N

    If Len(msg) > 1 Then
        MsgBox msg
    Else
        MsgBox "There are no code 1 values in Cod column."
    End If

End Sub