title: continuous loop using Find in Excel VBA
body: <p>I have the below code, which I am having trouble with:</p>
  
  <pre><code>Sub getAccNos()
  
  Dim oNameRange As Range
  Dim oFindRng As Range
  
  Dim sName As String
  Dim sAccNo As String
  
  Set oNameRange = Workbooks("New Name Work.xls").Worksheets("Manual").Range("B4")
  
  Do While Not oNameRange.Text = ""
      sName = Trim(oNameRange.Text)
      Workbooks("New Name Work.xls").Worksheets("sheet1").Select
      Set oFindRng = Cells.Find(What:=sName, After:=activecell)
  
      Do While Not oFindRng Is Nothing
          oNameRange.Offset(0, -1).Value = oFindRng.Offset(0, 1).Text
          oFindRng.Offset(1, 0).Activate
          Set oFindRng = Cells.Find(What:=sName, After:=activecell)
      Loop
      Set oNameRange = oNameRange.Offset(1, 0)
  Loop
  End Sub
  </code></pre>
  
  <p>Basically, on worksheet <strong>sheet1</strong> I have a list of names with account number, and there can be several account numbers with the same name. On my target sheet, called <strong>Manual</strong>, I have the names .... but the account numbers are missing and I would like to get them.</p>
  
  <p>I cannot use VLOOKUP because there are several names that are the same and I need to get a list of all the account numbers. How can I do this?</p>
  
  <p>I tried to write the above code using FIND in VBA, unfortunately, I am missing something elementary as once in the inside Do Loop it just loops continuously when it should be stepping out (as for the first one there is only one occurrance)</p>
  
  <p>thanks for showing me what I am doing wrong, or maybe a formula would be better?</p>