title: Loop Up rows until Value DOES NOT equal Continued
body: <p>This is a small section of a larger code. basically if the cell contains the word continued I need to look in the cell above, if this cell contains the word continued then I need to continue to loop up the rows until a value which is not continued is found. This is what I have so far ?</p>
  
  <pre><code>      Do
              If .Cells(SourceCell.Row, 3).Value = "continued." Then
                wsFHA.Cells(RowCounter, 7).Value = .Cells(SourceCell.Offset(rowoffset:=-1).Row, 3).Value
  
              End If
        Loop Until .Cells(SourceCell.Row, 3).Value &lt;&gt; "continued."
  </code></pre>
  
  <p>The code above is a smaller part of this code the code is to search for failure mode and causes. However in the source data sometimes the same value is repeated. in this case the word continued appears in the cell and you must refer to the information in the cell above. However for the purposes of coalating the data i need the actual information not the word continued. im trying to make the code find this information but am struggling.</p>
  
  <pre><code>Sub Create_FHA_Table()
      Dim Headers() As String: Headers = _
      Split("FHA Ref,Engine Effect,Part No,Part Name,FM I.D,Failure Mode &amp; Cause,FMCM,PTR,ETR", ",")
  
      If Not WorksheetExists("FHA") Then Worksheets.Add().Name = "FHA"
      Dim wsFHA As Worksheet: Set wsFHA = Sheets("FHA")
      wsFHA.Move after:=Worksheets(Worksheets.Count)
      wsFHA.Cells.Clear
  
      Application.ScreenUpdating = False
  
      With wsFHA
          For i = 0 To UBound(Headers)
              .Cells(2, i + 2) = Headers(i)
              .Columns(i + 2).EntireColumn.AutoFit
          Next i
          .Cells(1, 2) = "FHA TABLE"
          .Range(.Cells(1, 2), .Cells(1, UBound(Headers) + 2)).MergeCells = True
          .Range(.Cells(1, 2), .Cells(1, UBound(Headers) + 2)).HorizontalAlignment = xlCenter
          .Range(.Cells(1, 2), .Cells(2, UBound(Headers) + 2)).Font.Bold = True
      End With
  
  
  
      Dim RowCounter As Long: RowCounter = 3
      Dim SearchTarget As String 'must copy and paste between these bookmarks for each new code, "SearchTarget#"
      SearchTarget = "9.1" 'Must update SearchTarget#
      Dim SourceCell As Range, FirstAdr As String
  
      If Worksheets.Count &gt; 1 Then
          For i = 1 To Worksheets.Count - 1
          With Sheets(i)
              Set SourceCell = .Columns(7).Find(SearchTarget, LookAt:=xlWhole) 'Must Update SearchTarget# to correspond with above
              If Not SourceCell Is Nothing Then
                  FirstAdr = SourceCell.Address
                  Do
                      wsFHA.Cells(RowCounter, 2).Value = SearchTarget 'Must Update SearchTarget# to correspond with above
                      wsFHA.Cells(RowCounter, 3).Value = .Cells(SourceCell.Row, 6).Value
                      wsFHA.Cells(RowCounter, 4).Value = .Cells(3, 10).Value
                      wsFHA.Cells(RowCounter, 5).Value = .Cells(2, 10).Value
                      wsFHA.Cells(RowCounter, 6).Value = .Cells(SourceCell.Row, 2).Value
                      wsFHA.Cells(RowCounter, 7).Value = .Cells(SourceCell.Row, 3).Value
  
  
                      If .Cells(SourceCell.Row, 3).Value = "continued." Then
                        wsFHA.Cells(RowCounter, 7).Value = .Cells(SourceCell.Offset(rowoffset:=-1).Row, 3).Value
                      End If
  
  
  
  
                      wsFHA.Cells(RowCounter, 8).Value = .Cells(SourceCell.Row, 14).Value
                      Set SourceCell = .Columns(7).FindNext(SourceCell)
                      RowCounter = RowCounter + 1
                  Loop While Not SourceCell Is Nothing And SourceCell.Address &lt;&gt; FirstAdr
              End If
          End With
          Next i
      End If
  
  
  
  
  
  
      Application.ScreenUpdating = True
  
  
  End Sub
  
  Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
      On Error Resume Next
      WorksheetExists = (ThisWorkbook.Sheets(WorksheetName).Name &lt;&gt; "")
      On Error GoTo 0
  </code></pre>