title: finding the first and last entry with a certain value in a row
body: <p>I have a excel sheet containing multiple cells with a string <code>foo</code> in the first row. I want to find the first and last column in which the string is written. I have tried the following</p>
  
  <pre><code>Dim first_col As Integer
  Dim last_col As Integer
  Dim col As Integer
  Dim found As Range
  Dim ws_MLB as Worksheet
  Dim foo as String
  
  set ws_MLB = ThisWorkbook.Sheet(1)
  
  Set found = ws_MLB.Rows(1).Find(foo)
  If Not found Is Nothing Then
      col = found.Column
      first_col = col
      last_col = col
      Do
          found = ws_MLB.Rows(1).FindNext(found)
          col = found.Column
  
          If col &lt; first_col Then
              first_col = col
              MsgBox ("This should not happen")
          ElseIf col &gt; last_col Then
              last_col = col
          End If
      Loop While Not found Is Nothing And col &lt;&gt; first_col
  Else
      MsgBox ("not found")
  End If
  </code></pre>
  
  <p>But this way I only get the the first value for both first_col and last_col. When I search for the string with the integrated excel search I find multiple instances. So the string is there. Have I done a mistake or is there a better way to do this?</p>
  
  <p>edit forgot to mention that I also tried to change the search direction, but I still got the first entry.</p>