Find Text, Cut & Paste It In Another Column

[dgr]

Hello,
I'm using Excel 2013.

I want to find a particular text, cut & paste it in a column of my choice within the same row. This text may be present in any column. I have gone as far as the code below which works fine. However, the code stops half way though there is more rows to be searched. I'm not sure why but I think it's got something to do with the possibility that there may be duplicate text in the same row which is causing the code to fail (or it could be something else, I really don't know).

So my question is how do I get this code to complete the job without stopping halfway? If there are duplicate text in the same row, how do I make the code cut & paste the 1st found text, ignore the duplicate & move on to the next row?

Thanks for your help.

Sub find_anything_move_to_next_column()
    
    Dim rngFind As Range
    Dim strAddress As String
    
    Range("A1").Select
    With ActiveSheet.UsedRange

'the text that I want to find is below
        Set rngFind = .Find(" sq.ft.", LookIn:=xlValues, lookat:= _
    xlPart, MatchCase:=True)
  
'I want to move the found text to column E      
        If Not rngFind Is Nothing Then
            Do
                If rngFind.Column <> 5 Then
                    If rngFind.EntireRow.Cells(1, 5) <> "" Then Exit Do
                    rngFind.Copy rngFind.EntireRow.Cells(1, 5)
                    rngFind.Clear
                Else
                    If strAddress = "" Then
                        strAddress = rngFind.Address
                    Else
                        If rngFind.Address = strAddress Then
                            ' back to begining
                            Exit Do
                        End If
                    End If
                End If
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing
        End If
    End With
End Sub

 

[Andrew Poulsom]

Your code works for me. It will stop if there is already something in column E, because of this line:

If rngFind.EntireRow.Cells(1, 5) <> "" Then Exit Do

 

[dgr]

Thanks Andrew for the insight. There is nothing in Column E. A sample of my Excel file is located at https://www.dropbox.com/s/eqolg0jyje9akju/Book2.xlsx?dl=0

I would be grateful if you could run the macro on this Excel file. Meanwhile, I'll try to remove

If rngFind.EntireRow.Cells(1, 5) <> "" Then Exit Do

& see if it helps.

Thank you once again for your guidance.

 

[dgr]

Hi Andrew, you were right. I just removed that line & everything worked fine. I appreciate your guidance on this matter.