In Excel, do a Search, Copy Paste loop that will stop when the Search fails

Question

I want a macro that will copy all rows in one sheet that have a certain name in them to a separate sheet.

My plan is to do it as a loop that stops when it can not find any more of the name. The problem is I can't figure out how to make the loop stop when the search fails when it has found all the occurrences.

Here is my code that loops 10 times. It works just fine except that I want to fix it so that it loops however many it takes and then stops. This could be anywhere from 0 times to 500 times.

By the way, the values I search for are in 3 different columns side by side.

I would really like to change the code as little as possible as I don't know VBA well and would like to avoid doing a lot of learning that I will be unlikely to use again.

    Dim Counter As Integer

    Range("A1").Select

    ' Start the loop that I want changed to stop automatically: 

    Do While Counter < 10

    Cells.Find(What:="matt johnson", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate


    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Copy
    ' go to destination sheet:
    Sheets("Matt").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 2).Range("A1").Select
    Selection.End(xlToLeft).Select
    ActiveSheet.Paste
    ' go back to source sheet:
    ActiveCell.Offset(1, 0).Range("A1").Select
    Sheets("Upcoming Deadlines").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 26).Range("A1").Select
    Application.CutCopyMode = False

    Counter = Counter + 1

    Loop

End Sub

You don't need a loop. Use `Find/FindNext`. See [THIS](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) and if the data is in the same column then you dont need `Find/FindNext`. Use `Autofilter`. See [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) — Siddharth Rout, Apr 28 '14 at 06:44
and also: [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, Apr 28 '14 at 08:22
Thanks Siddharth, but the data is in 3 columns not 1, and I don't know how to use Find/FindNext instead of a loop. I would be willing to have a solution that is not super robust as this is not a large program. — Tom Panek, Apr 28 '14 at 22:45

score 0 · Answer 1 · answered Sep 19 '14 at 19:07

I think this will solve your problem with minimal changes:

Sub test()
    Dim Counter As Integer

    Range("A1").Select

    ' Start the loop that I want changed to stop automatically:

Do Until Cells.Find(What:="matt johnson", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False) Is Nothing

    Cells.Find(What:="matt johnson", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate


    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Copy
    ' go to destination sheet:
    Sheets("Matt").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 2).Range("A1").Select
    Selection.End(xlToLeft).Select
    ActiveSheet.Paste
    ' go back to source sheet:
    ActiveCell.Offset(1, 0).Range("A1").Select
    Sheets("Upcoming Deadlines").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 26).Range("A1").Select
    Application.CutCopyMode = False

    Counter = Counter + 1

Loop

End Sub

Explanation

This will continue to loop until the search finds nothing.

In Excel, do a Search, Copy Paste loop that will stop when the Search fails

1 Answers1

Explanation