title: Looping findnext inside another loop & find
body: <p>I have used a loop to find the closest name to a supplier from Sheet 1 out of Sheet 2. </p>
  
  <pre><code>Dim LastRow As Long
  LastRow = Sheets("BBB").Range("A" &amp; Rows.Count).End(xlUp).Row
  
  Dim i As Integer
  For i = 2 To LastRow
  Dim ra As Range
  Dim a, k As Integer
  a = Len(Sheets("BBB").Range("A" &amp; i))   
  
  Do
  Set ra = Sheets("AAA").Cells.Find(What:=Left(Range("A" &amp; i), a), LookIn:=xlValues, LookAt _
          :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False)
  
  
  a = a - 1
  
  Loop Until Not ra Is Nothing Or a = 3
  
  If ra Is Nothing Then
  Sheets("BBB").Range("C" &amp; i).Value = a
  Else
  Sheets("BBB").Range("B" &amp; i).Value = ra.Value
  </code></pre>
  
  <p>It works great but now I am thinking that It is possible that some occurences are twice in the sheet "AAA"</p>
  
  <p>Example: 
  Supplier in Sheet BBB: "SICK"
  If Sheet AAA has 2 suppliers: "SICK" and "NOSICKHERE LTD"
  My code will only find one of the two supplier but will not return both. </p>
  
  <p>How can I use findnext to find all occurences? 
  Anyone see a better solution? </p>
  
  <p>I tried to use the following at the bottom of my code before the "next i", but I fail to use the findnext</p>
  
  <pre><code>Dim firstCellAddress As String
  firstCellAddress = ra.Address
  
  k = 1
  Do
  
      Set ra = Sheets("AAA").Cells.FindNext()
      Sheets("BBB").Cells(i, 2 + k).Value = ra.Value
  
      k = k + 1
  
  Loop While firstCellAddress &lt;&gt; ra.Address
  </code></pre>
  
  <p>Please tell me if my question is too hard to understand</p>