Find and move rows

I need help with a macro that can check sheet 1 which has content as this


http://i72.photobucket.com/albums/i173/or.jpg
http://i72.photobucket.com/albums/i173/ssor.jpg
http://i72.photobucket.com/albums/i173/rror.jpg

Column A has this

Sheet 2 has many columns of data and many rows

Need to search full content and move the complete row if found as column A of sheet 1 to sheet 3

Whole row should be copied or cut either ways is fine with me
LVL 11
bsharathAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try this .... not tested
Sub getfoundurls()
    Dim cel As Range, fs2 As Range
    For Each cel In Sheet1.UsedRange.Columns(1).Cells
        Set fs2 = Sheet2.UsedRange.Find(cel, , , xlWhole)
        If Not fs2 Is Nothing Then
            fs2.EntireRow.Copy Sheet3.Cells.SpecialCells(xlCellTypeLastCell).Offset(1).EntireRow
        else 
            cel.interior.color = 255
        End If
    Next cel

Open in new window

End Sub
0
 
hanklmCommented:
FYI:  The image links don't seem work - at least not for me.

I get redirected here: http://beta.photobucket.com/browse?httpstatus=404

Maybe it's just a temporary issue?
0
 
Saqib Husain, SyedEngineerCommented:
It would be better if you upload the files on experts-exchange.

Better still if you can upload an excel file (possibly fictitious) so that direct testing could be done instead of rebuilding the data.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
bsharathAuthor Commented:
The links are dummy that i placed

These URl's 100's will be in sheet1 column A

Find each in sheet 2 and copy full row to sheet 3
0
 
bsharathAuthor Commented:
Any help with this please?
0
 
Saqib Husain, SyedEngineerCommented:
Please throw in a sample file to work on.
0
 
bsharathAuthor Commented:
Please find attached sample file
Sample.xls
0
 
Saqib Husain, SyedEngineerCommented:
Here you go

Sub getfoundurls()
    Dim cel As Range, fs2 As Range
    For Each cel In Sheet1.UsedRange.Columns(1).Cells
        Set fs2 = Sheet2.UsedRange.Find(cel, , , xlWhole)
        If Not fs2 Is Nothing Then
            fs2.EntireRow.Copy Sheet3.Cells.SpecialCells(xlCellTypeLastCell).Offset(1).EntireRow
        End If
    Next cel
End Sub
0
 
bsharathAuthor Commented:
Thanks

Anyway i can color sheet 1 cells whose content is not found in sheet 2 and was skipped
0
 
bsharathAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.