title: using Do Until to find specific text in Excel VBA
body: <p>I need to enter a formula to extract a portion of text from specific cells. This needs to be repeated an unknown number of times. I am using a do statement with the find function. Unfortunately it will only find the first occurance and loop continously. Is there a way to force finding the next value?
  Can anyone help me?</p>
  
  <p>Here is the code I am using:</p>
  
  <pre><code>    Do
  
      With Columns("B")
          .Find(What:="total", After:=.Cells(1, 1), LookIn:=xlValues).Activate
      End With
  
       ActiveCell.Offset(0, -1).Select
       ActiveCell.FormulaR1C1 = "=LEFT(RC[1],12)"
       ActiveCell.Offset(1, 0).Activate
       ActiveCell.FormulaR1C1 = "=LEFT(RC[1],12)"
  
  
  
      Loop Until ActiveCell.Offset(0, 1) = "Grand Total"
  
  
      End Sub
  
  Thank you for your assistance.
  jfabro
  
  I have been able to make the macro work to loop through the entire workseet however it does not stop when the condition is met. I believe the problem lies in the condition. What I am needing to say is to stop if the cell to the right of the active cell contains Grand Total.
  
  Here is the new code I am using:
  Do Until ActiveCell.Offset(0, 1) = "*Grand Total*"
  
  With Columns("B")
      Cells.Find(What:="total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
          xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
          , SearchFormat:=False).Activate
  End With
  
   ActiveCell.Offset(0, -1).Select
   ActiveCell.FormulaR1C1 = "=LEFT(RC[1],12)"
   Cells.FindNext(After:=ActiveCell).Activate
  
  If ActiveCell.Offset(0, 1) = "*Grand Total*" Then
  
  
  Exit Do
  
  End If
  
  Loop
  
  
  
  
  
  End Sub
  </code></pre>