Find Text, Cut & Paste It In Another Column

dgr

Board Regular
Joined
Apr 24, 2005
Messages
176
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.
Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Your code works for me. It will stop if there is already something in column E, because of this line:

Rich (BB code):
If rngFind.EntireRow.Cells(1, 5) <> "" Then Exit Do
 
Upvote 0
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
Code:
If rngFind.EntireRow.Cells(1, 5) <> "" Then Exit Do
& see if it helps.

Thank you once again for your guidance.
 
Upvote 0
Hi Andrew, you were right. I just removed that line & everything worked fine. I appreciate your guidance on this matter.
 
Upvote 0

Forum statistics

Threads
1,222,045
Messages
6,163,583
Members
451,846
Latest member
ajk99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top