+ Reply to Thread
Results 1 to 11 of 11

Changing the index of a for loop inside of the loop

  1. #1
    Registered User
    Join Date
    08-01-2011
    Location
    California
    MS-Off Ver
    2007/2010
    Posts
    14

    Changing the index of a for loop inside of the loop

    My code starts with a defined range from k = 2 to the total number of rows in the worksheet. The k is used to identify the row I'm at within the worksheet. TotNumOfUsedRows is found by Worksheets.UsedRange.Rows.Count. Within my loop I insert rows if certain criteria is met. Because I still need the program to go to the end of the used rows, I increase both my k and my total # of rows. I'm finding that it is exiting early, and it appears to do so when k = 1 more than the original TotNumOfUsedRows.

    Sub InsertRows()
    TotNumOfUsedRows = Worksheets("Name").UsedRange.Rows.Count

    for k = 2 to TotNumOfUsedRows

    logic here that sometimes adds a row(s), sometimes doesn't. Indexes with k to determine which row it's on

    k = k + number of rows added
    TotNumOfUsedRows = Worksheets("Name").UsedRange.Rows.Count

    Next k

    Can I change k and TotNumOfUsedRows within the for loop and have it be used as desired?

    Thanks.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Changing the index of a for loop inside of the loop

    use a do until loop and have a variable for the (lngEndOfData) which you increase by one when you add a row?

  3. #3
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Changing the index of a for loop inside of the loop

    sorry TotNumOfUsedRows not lngEndOfData

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Changing the index of a for loop inside of the loop

    Try looping backwards.

    Oh, and please use code tags.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  5. #5
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Changing the index of a for loop inside of the loop

    Reverse the direction of the loop (Go Bottom to top)....... Start at the bottom and step up because when you insert a row and then "step" you miss one row of data.
    Elegant Simplicity............. Not Always

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,272

    Re: Changing the index of a for loop inside of the loop

    Short answer: no.

    I would suggest that, when you want to add a row, you add it and use "Exit For". The test to see if the row you got up to is greater than the total rows and re-initiate the loop starting from where you left off.

    Alternatively, work your way up from the bottom, "total number of rows to 2 step -1" inserting rows as you go. That's probably an easier option.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  7. #7
    Registered User
    Join Date
    08-01-2011
    Location
    California
    MS-Off Ver
    2007/2010
    Posts
    14

    Re: Changing the index of a for loop inside of the loop

    Thanks for the replies. Is the For loop not recognizing that the start and end parameters have changed? Why? For example, when I run it first, it tells me k = 2 and TotNumOfUsedRows = 17 (both correct). When I MsgBox() these values within the for loop they look correct at all times; k increases correctly and TotNumOfUsedRows does too. However, when I MsgBox(k) right before exiting the program, k = 18, which is what you'd expect if it was just going k=2 to 17. MsgBox(TotNumOfUsedRows) = 25 at end.


    I believe I have accounted for the step part within it. Everything is built off of going top to bottom, and the data is chronological top to bottom, with a bunch of different nuances, so I'd rather not change it at this point!

    Thank you all very much

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,603

    Re: Changing the index of a for loop inside of the loop

    Changing the loop control variable and the loop limits within the loop is not a good idea.

    Why not try looping bottom to top?

    If it doesn't work post the rest of the code or better still upload a sample workbook, then we can see the nuances of the data.

  9. #9
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Changing the index of a for loop inside of the loop

    the control indices are only evaluated once at the start so you have to loop backwards as suggested or use a different construct (as also suggested)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  10. #10
    Registered User
    Join Date
    08-01-2011
    Location
    California
    MS-Off Ver
    2007/2010
    Posts
    14

    Re: Changing the index of a for loop inside of the loop

    That makes sense and it's good to know that this method won't work. I am going to play around with starting from bottom and working my way up. Thank you all.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,272

    Re: Changing the index of a for loop inside of the loop

    You're welcome.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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