title: Finding specific number inside a string?
body: <p>This is a sample of my data, where <code>name</code> is in cell <code>A1</code>. The column C is not part of the data, it is there just to illustrate what is wanted.</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>I am trying to write a macro to detect on the column <code>cod</code> where the number 1 appears. For example, I don't want to find 10, 13, 21, but <code>1</code>. The filled in numbers on this column go from 1 to 21.</p>
  
  <p>All <code>cod</code> values are strings, but I want to find where there is <code>1</code>, even if it appears mixed with other numbers in the string. Numbers in this column are always separated with <code>;</code> without white spaces in between.</p>
  
  <p>The following code will produce false <strong>positives</strong>:</p>
  
  <pre><code>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) &gt; 0 Then
                  msg = msg &amp; "Code 1 was not supposed to be in Cod column." &amp; vbLf
              Exit For
      End If
  Next N
  
      If Len(msg) &gt; 1 Then
          MsgBox msg
      Else: MsgBox "There are no code 1 values in Cod column."
      End If
  
  End Sub
  </code></pre>
  
  <p>See results:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>The following code will produce false <strong>negatives</strong>:</p>
  
  <pre><code>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) &gt; 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 &amp; "Code 1 was not supposed to be in Cod column." &amp; vbLf
          Exit For
      End If
  Next N
  
      If Len(msg) &gt; 1 Then
          MsgBox msg
      Else: MsgBox "There are no code 1 values in Cod column."
      End If
  
  End Sub
  </code></pre>
  
  <p>See results:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So, how could one make the message box* appear only when a number <code>1</code> is detected inside the string?</p>
  
  <blockquote>
    <p>*Code 1 was not supposed to be in Cod column.</p>
  </blockquote>
  
  <hr>
  
  <p><sub> Looking for a solution that works with Excel 2007 and newer versions. </sub></p>