+ Reply to Thread
Results 1 to 6 of 6

For/Next Loop

  1. #1
    Registered User
    Join Date
    12-12-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    For/Next Loop

    The macro is suppose to:
    1. search an entire sheet called "babies" for a given phrase ("puppies")
    2. copy each row with the phrase ("puppies")
    3. paste each row into the "dogs" sheet


    The for loop should use the countif function to tell it how many times to loop and each loop when copy/paste.
    I get the "code execution has been interrupted" error and the yellow error points to the "Next lloop" line.

    Any thoughts???

    [code]
    Sub complete()

    Dim lloop As Long
    Dim rFound As Range

    Set rFound = Range("A2")

    For lloop = 1 To WorksheetFunction.CountIf(Sheets("babies").Range("B:B"), "puppies")
    Set rFound = ActiveSheet.UsedRange.Find _
    (What:="puppies", After:=rFound, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    rFound.EntireRow.Copy _
    Destination:=Sheets("dogs").Cells(Rows.Count, 1).End(xlUp)(2, 1)
    Next lloop

    End Sub
    [code/]

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: * NEED HELP "For, Next" Loop does not work*

    Here you go. Please mark the thread as SOLVED if it was

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-12-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: For/Next Loop

    I dont know how to make that code do anything. I made 2 sheets (dogs & babies) and put in some data in the babies sheet and hit play. Nothing happened.

    It doesnt really help even if it did work tho because I dont want to change my logic to find strings. I just want to make the loop work...

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: For/Next Loop

    the code will search for the word puppies in column B then it will copy that row to the end of dogs sheet
    I attached a file with the macro in it to test it

  5. #5
    Registered User
    Join Date
    12-12-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: For/Next Loop

    It works now. I had to reinstall my microsoft office suite to fix my Excel.


    Thanks. I dont know how to mark this as SOLVED.

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: For/Next Loop

    To mark your thread solved do the following:
    - Go to the first post
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solve
    - Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1