Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing the 'end' value of a for loop

    HI all,

    Is it possible to change the end value of a for loop whilst in the actual loop?

    The reason Im asking is that Im looping through some cells in a spreadsheet and inserting rows depending on certain conditions. Im calculating the last row of the spreadsheet using this piece of script:

    finalrow = xlSht3.Cells(xlApp.Rows.Count, 6).End(xlUp).Row

    Im then using this value in a for loop as shown. Because Im inserting rows, I need to tell the loop to now stop at the new final row, not the original one. The problem seems to be that the loop still stops at the original value and not my updated value. Any ideas?

    Code:
    TotalColumnLength = 1
    For RowStart = 3 To finalrow
       If xlSht3.Cells(RowStart + 1, 2) = "" Then
          TotalColumnLength = TotalColumnLength + 1
       Else
          If TotalColumnLength <> 3 Then
             xlSht3.Cells(RowStart + 1, 2).EntireRow.Select
             If TotalColumnLength = 1 Then
                xlApp.Selection.Insert Shift:=xlDown
                xlApp.Selection.Insert Shift:=xlDown
                RowStart = RowStart + 2
                finalrow = finalrow + 2
             Else
                xlApp.Selection.Insert Shift:=xlDown
                   RowStart = RowStart + 1
                   finalrow = finalrow + 1
             End If
             TotalColumnLength = 1
          Else
             TotalColumnLength = 1
          End If
       End If
    Next RowStart
    Last edited by Gary Frieder; 2011-03-11 at 08:03. Reason: Indented code and added code tags for legibility

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    RowStart and FinalRow are evaluated once by Excel at the start of the loop.
    The RowStart and FinalRow values are not reevaluated during the loop.
    You will get uncertain results by trying the change those values during the loop. Don't do it.

    One thing you could do is use a counter inside the loop and exit the loop using "Exit For" when the counter reaches a specified value: Counter = Counter + 1
    As it is not clear to me exactly what the criteria is for quiting the loop, I can't advise further.
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (XL Companion add-in: compares, matches, counts, lists, finds, deletes...)

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You should loop backwards when you need to do this sort of thing:
    Code:
    For RowStart = finalrow to 3 step -1
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Or use:

    Code:
    RowStart = 3
    finalrow = xlSht3.Cells(xlApp.Rows.Count, 6).End(xlUp).Row
    Do While RowStart <= finalrow
      'Your code here; make sure to increment and recompute as needed
    Loop

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Jasonsas

    Start in revsrese. That is from the bottom going up the list and as you add rows the bottom will expand but the top is always going to be located at Row 1.

    Thninking outside the box is a good feeling some times.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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