+ Reply to Thread
Results 1 to 2 of 2

Moving from For...Next loop to subroutine and back to loop

  1. #1
    Registered User
    Join Date
    04-25-2011
    Location
    DC, USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Moving from For...Next loop to subroutine and back to loop

    Question: I'm looking to divide a large number of records into separate worksheets based on a for...next loop that runs through the file records and searches for text strings in a field. based on that information, it copies the record into the appropriate worksheet. My code's below.

    The problem: After running the subroutine, I don't know how to get VBA to head back into the original For...Next loop in the main routine. help? (Also, I'm sure the code below isn't elegant. If there's a better solution -- i'll wind up having to write a number of subs that mimic jdoe -- please feel free to suggest). Thanks.


    Sub tstStreamDivn()

    Dim loopcounter As Integer
    Dim rowcount As Integer
    Dim strSubject As String

    Worksheets("Streams").Range("a1").Activate
    rowcount = ActiveSheet.UsedRange.Rows.Count

    For loopcounter = 1 To rowcount Step 1

    Range("a1").Offset(loopcounter, 0).Activate
    strSubject = ActiveCell.Value

    If InStr(1, strSubject, "John Doe") > 0 Then
    jdoe
    End If

    Next loopcounter

    End Sub

    Sub jdoe()

    Dim intjdoecount As Integer

    ActiveCell.EntireRow.Copy
    intjdoecount = intjdoecount + 1
    Sheets("jdoe").Activate
    ActiveCell.Offset(intjdoecount, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    End Sub

  2. #2
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Moving from For...Next loop to subroutine and back to loop

    The problem isn't that the rest of the For...Next loop isn't running. The problem is that variables only live as long as the procedures they live in. Specifically "intjdoecount = intjdoecount + 1" doesn't do anything because the variable is destroyed at "End Sub" of that procedure. So, each iteration of the For...Next loop means that the John Doe row is being copied to row 1, overwriting the previous values copied.

    The code beclow should work for you. Notice that I got rid of "Activate" commands. There is rarely a need to activate something to work with it in code. You could run this code with any sheet activated and it would still work as desired.

    Please Login or Register  to view this content.
    A further note:
    If you click "Debug" -> "Step Into" (or just press F8), you can watch the code process one line at a time. You will see that your code does, in fact, go back to the For...Next loop after running through the jdoe() subroutine. Walking through the steps like this is a great way to debug your code and see what is actually happening.
    Last edited by Whizbang; 04-25-2011 at 01:33 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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