For Next Loop End condition cannot be changed in Loop. Do While Loop End condition can be changed in

DocAElstein

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hello,
. Sorry that the following question/ problem looks very fundamental:nya:. But I have not found the answer in any books, or by "Googling" etc. yet!!
. I am trying to reorganize very large files with VBA and amongst other things I sometimes need to insert or delete lines within a loop. It is sometimes then necessary to take account of this by adjusting the loop count or the loop end within the loop (see for example http://www.mrexcel.com/forum/excel-...%3D-y-%96-1-usedrange-rows-count-anomale.html ) .
. I have noticed something strange. I can alter the value of the loop count ( y As Integer) within the loop. But if I try to change the end of the loop (FinalRow As Integer), it does not work, that is to say VBA ignores the new stop value and leaves the loop at the original value.
. I demonstrate the problem as follows:-
“For Next” Loops​
. This first very simple code gives me exactly what I expect. That is, it sticks an arbitrary number (in this case the loop number) in rows 1-10, cell 1. The result is exactly what I expect:-
1
2
3
4
5
6
7
8
9
10

Code: 
[COLOR=#0000ff]Sub[/COLOR] ForNext1SimpleLoop() [COLOR=#00ff00]'Simple 1-10 loop[/COLOR]
  [COLOR=#0000ff] On Error GoTo[/COLOR] Err:
  [COLOR=#0000ff] Dim[/COLOR] y [COLOR=#0000ff]As Integer[/COLOR], FinalRow [COLOR=#0000ff]As Integer[/COLOR], LoopCount [COLOR=#0000ff]As Integer[/COLOR]
  [COLOR=#0000ff] Let[/COLOR] LoopCount = 0
   [COLOR=#0000ff]Let[/COLOR] FinalRow = 10
        [COLOR=#0000ff]For [/COLOR]y = 1 [COLOR=#0000ff]To[/COLOR] FinalRow
        [COLOR=#0000ff]Let[/COLOR] LoopCount = LoopCount + 1
        Cells(y, 1).Value = LoopCount
            Application.StatusBar = "Loop Count is " & LoopCount & ".   Row is " & y & ".  Final Row is " & FinalRow & "."
        [COLOR=#0000ff]Next[/COLOR] y
   MsgBox ("Done!")
   Cells.Columns(1).Clear
Err:
Application.StatusBar = False
[COLOR=#0000ff]End Sub[/COLOR][COLOR=#00ff00] 'ForNext1SimpleLoop[/COLOR]()


………………………………………………………

. In the next code I am inserting an extra line between these values and so allow for this by incrementing the loop count, y, by 1 in the loop. As I expect I only get my first 5 data values, as the end of the loop has not been changed
1

2

3

4

5

Code: 
[COLOR=#0000cd]Sub [/COLOR]ForNext2InsertExtraRow() [COLOR=#00ff00]'Simple loop which inserts an extra Row at every Row[/COLOR]
   On Error GoTo Err:
   [COLOR=#0000cd]Dim y[/COLOR] [COLOR=#0000cd]As Integer[/COLOR], FinalRow [COLOR=#0000cd]As Integer[/COLOR], LoopCount [COLOR=#0000cd]As Integer[/COLOR]
   Le[COLOR=#0000cd]t L[/COLOR]oopCount = 0
   [COLOR=#0000cd]Let F[/COLOR]inalRow = 10
        Fo[COLOR=#0000cd]r y[/COLOR] = 1 [COLOR=#0000cd]To[/COLOR] FinalRow
       [COLOR=#0000cd] Let L[/COLOR]oopCount = LoopCount + 1
        Cells(y, 1).Value = LoopCount
        Rows(y).Resize(1).Insert [COLOR=#00ff00]'Add an empty Row[/COLOR]
        [COLOR=#0000cd]Let y[/COLOR] = y + 1 [COLOR=#00ff00]' Ensure that the next y goes to the next empty Row[/COLOR]
            Application.StatusBar = "Loop Count is " & LoopCount & ".   Row is " & y & ".  Final Row is " & FinalRow & "."
        Ne[COLOR=#0000cd]xt y[/COLOR]
   MsgBox ("Done!")
   Cells.Columns(1).Clear
Err:
Application.StatusBar = False
[COLOR=#0000cd]End Sub [/COLOR][COLOR=#00ff00]'ForNext2InsertExtraRo[/COLOR]w()

……………………………………………………………

In the next code I expected to get
1

2

3

4

5

6

7

8

9

10
… as I am also incrementing the loop stop value, FinalRow. (Indeed, By stepping through with F8 one can confirm that the value of FinalRow does in fact change in each loop and obtains a final value of 20)


Code: 
[COLOR=#0000cd]Sub[/COLOR] ForNext3InsertExtrarowAndAttemptChangeLoopEnd() [COLOR=#00ff00]'Attempt to change Loop end within the loop[/COLOR]
   [COLOR=#0000cd]On Error GoTo[/COLOR] Err:
  [COLOR=#0000cd] Dim[/COLOR] y [COLOR=#0000cd]As Integer[/COLOR], FinalRow [COLOR=#0000cd]As Integer[/COLOR], LoopCount [COLOR=#0000cd]As Integer[/COLOR]
   [COLOR=#0000cd]Let L[/COLOR]oopCount = 0
  [COLOR=#0000cd] Let[/COLOR] FinalRow = 10
      [COLOR=#0000cd]  For [/COLOR]y = 1 [COLOR=#0000cd]To[/COLOR] FinalRow
      [COLOR=#0000cd]  Let L[/COLOR]oopCount = LoopCount + 1
        Cells(y, 1).Value = LoopCount
        Rows(y).Resize(1).Insert [COLOR=#00ff00]'Add an empty Row[/COLOR]
        [COLOR=#0000cd]Let [/COLOR]y = y + 1[COLOR=#00ff00] ' Ensure that the next y goes to the next empty Row[/COLOR]
        [COLOR=#0000cd]Let [/COLOR]FinalRow = FinalRow + 1 ' Increase the loop stop value to account for the new Row
            Application.StatusBar = "Loop Count is " & LoopCount & ".   Row is " & y & ".  Final Row is " & FinalRow & "."
      [COLOR=#0000cd]  Next[/COLOR] y
   MsgBox ("Done!")
   Cells.Columns(1).Clear
Err:
Application.StatusBar = [COLOR=#0000cd]False[/COLOR]
 
[COLOR=#0000cd]End Sub [/COLOR][COLOR=#00ff00]'ForNext3InsertExtrarowAndAttemptChangeLoop[/COLOR]End()

. HOWEVER I obtain the same results as in the second example, Sub looptest2(). VBA appears to ignore a change in the end of the loop made within the loop?
. Can anyone explain this to me? If the answer is that VBA sets the end of the loop at the start, then I find it strange that
1):- that it allows y to be changed. As a beginner, I would have thought that at the point “ For y = 1 To FinalRow “ the current value of y And FinalRow would be checked. Possibly there is a more complex explanation to do with how the different variables, y and FinalRow are stored internally by VBA. However, :-
2):- Do While loops do not appear to have this Problem/ characteristic. The corresponding line “ Do While y < FinalRow “ appears to take account of a modified (in the loop) value of FinalRow

. I have, incidentally noticed that by stepping through the codes with F8, the “ For y = 1 To FinalRow “ in the For Next Loop is only highlighted in yellow at the start. In the case of the Do While Loop the corresponding line “ Do While y < FinalRow “ is highlighted additionally at every new loop.
. This appears to indicate an important difference in For Next and Do While Loops, which until now I have not read in any books, (And I have most of them!!)

Thanks
Alan ):-

P.s. The codes may be easier for you to examine in a Full File: The file with all the codes in .xlsm and “Zipped” form (made in Excel 2007 and I downloaded and checked in 2007 and 2010), - should be able to be downloaded simply by clicking on the following “Filesnack” links:-

FileSnack | Easy file sharing


FileSnack | Easy file sharing



P.P.s. Here underneath are some other codes (Also included in the above files) which do not have this problem. (But I would still like to know why the third code, Sub looptest3() does not give me the answer I expect, as it may be the indication of an important fundamental point (Pointer?!?) in VBA that I haven’t got yet!!)

“Home made! Loops”​

Code: 
[COLOR=#0000FF]Sub[/COLOR] SimpleManuelLoop() [COLOR=#00FF00]' "Home-Made" Simple Loop[/COLOR]
   [COLOR=#0000FF]On Error GoTo[/COLOR] Err:
   [COLOR=#0000FF]Dim[/COLOR] y [COLOR=#0000FF]As Integer,[/COLOR] FinalRow [COLOR=#0000FF]As Integer[/COLOR], LoopCount [COLOR=#0000FF]As Integer[/COLOR]
 [COLOR=#0000FF]  Let[/COLOR] LoopCount = 0
  [COLOR=#0000FF] Let [/COLOR]FinalRow = 10
  [COLOR=#0000FF] Let[/COLOR] y = 0
LoopStart:
       [COLOR=#0000FF] Let [/COLOR]y = y + 1
        [COLOR=#0000FF]Let[/COLOR] LoopCount = LoopCount + 1
        Cells(y, 1).Value = LoopCount
            Application.StatusBar = "Loop Count is " & LoopCount & ".   Row is " & y & ".  Final Row is " & FinalRow & "."
       [COLOR=#0000FF] If [/COLOR]y < FinalRow [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#0000FF]GoTo[/COLOR] LoopStart
        [COLOR=#0000FF]Else
        End If[/COLOR]
   MsgBox ("Done!")
   Cells.Columns(1).Clear
Err:
Application.StatusBar = False
[COLOR=#0000FF]End Sub[/COLOR] [COLOR=#00FF00]'SimpleManuelL[/COLOR]oop()

……………..

Code: 
Su[COLOR=#0000cd]b M[/COLOR]anuelLoopInsertExtrarowAndChangeLoopEnd() [COLOR=#00ff00]' "Home-Made" Loop. Insert Extra Row and Change Loop End[/COLOR]
   [COLOR=#0000cd]On Error GoTo[/COLOR] Err:
   [COLOR=#0000cd]Dim y[/COLOR] [COLOR=#0000cd]As Integer[/COLOR], FinalRow [COLOR=#0000ff]As Integer[/COLOR], LoopCount[COLOR=#0000ff] As Integer[/COLOR]
   Le[COLOR=#0000ff]t L[/COLOR]oopCount = 0
  [COLOR=#0000ff] Let[/COLOR] FinalRow = 10
   [COLOR=#0000ff]Let[/COLOR] y = 0
LoopStart:
        L[COLOR=#0000ff]et y[/COLOR] = y + 1
       [COLOR=#0000ff] Let[/COLOR] LoopCount = LoopCount + 1
        Cells(y, 1).Value = LoopCount
        Rows(y).Resize(1).Insert [COLOR=#00ff00]'Add an empty Row[/COLOR]
        L[COLOR=#0000ff]et y [/COLOR]= y + 1[COLOR=#00ff00] ' Ensure that the next y goes to the next empty Row[/COLOR]
        [COLOR=#0000ff]Let[/COLOR] FinalRow = FinalRow + 1 ' Increase the loop stop value to account for the new Row
            Application.StatusBar = "Loop Count is " & LoopCount & ".   Row is " & y & ".  Final Row is " & FinalRow & "."
       [COLOR=#0000ff] If[/COLOR] y < FinalRow [COLOR=#0000ff]Then[/COLOR]
       [COLOR=#0000ff] GoTo [/COLOR]LoopStart
    [COLOR=#0000ff]    Else
        End If[/COLOR]
   MsgBox ("Done!")
   Cells.Columns(1).Clear
Err:
Application.StatusBar = Fa[COLOR=#0000ff]lse[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR] [COLOR=#00ff00]'ManuelLoopInsertExtrarowAndChangeLoo[/COLOR]pEnd()

…………………………..

Or “Do While loops”​

Code: 
[COLOR=#0000ff]Sub[/COLOR] SimpleDoWhileLoop() [COLOR=#00ff00]'Simple 1-10 loop[/COLOR]
   On[COLOR=#0000ff] Error GoTo E[/COLOR]rr:
  [COLOR=#0000ff] Dim [/COLOR]y [COLOR=#0000ff]As Integer[/COLOR], FinalRow [COLOR=#0000ff]As Integer[/COLOR], LoopCount [COLOR=#0000ff]As Integer[/COLOR]
  [COLOR=#0000ff] Let[/COLOR] LoopCount = 0
 [COLOR=#0000ff]  Let [/COLOR]FinalRow = 10
         D[COLOR=#0000ff]o While y[/COLOR] < FinalRow
       [COLOR=#0000ff]  Let[/COLOR] y = y + 1
        [COLOR=#0000ff] Let[/COLOR] LoopCount = LoopCount + 1
         Cells(y, 1).Value = LoopCount
            Application.StatusBar = "Loop Count is " & LoopCount & ".   Row is " & y & ".  Final Row is " & FinalRow & "."
         Loop
   MsgBox ("Done!")
   Cells.Columns(1).Clear
Err:
Application.StatusBar = [COLOR=#0000ff]False[/COLOR]
[COLOR=#0000ff]End Sub [/COLOR][COLOR=#00ff00]'SimpleDoWhileLo[/COLOR]op()

………………………..

Code: 
[COLOR=#0000ff]Sub[/COLOR] DoWhileLoopInsertExtrarowAndChangeLoopEnd() [COLOR=#00ff00]'Insert Extra Row and Change Loop End[/COLOR]
   [COLOR=#0000ff]On Error GoTo[/COLOR] Err:
  [COLOR=#0000ff] Dim[/COLOR] y [COLOR=#0000ff]As Integer[/COLOR], FinalRow [COLOR=#0000ff]As Integer[/COLOR], LoopCount [COLOR=#0000ff]As Integer[/COLOR]
   Le[COLOR=#0000ff]t L[/COLOR]oopCount = 0
   [COLOR=#0000ff]Let[/COLOR] FinalRow = 10
   L[COLOR=#0000ff]et y[/COLOR] = 0
         Do While y < FinalRow
         [COLOR=#0000ff]Let y[/COLOR] = y + 1
         Le[COLOR=#0000ff]t L[/COLOR]oopCount = LoopCount + 1
         Cells(y, 1).Value = LoopCount
         Rows(y).Resize(1).Insert [COLOR=#00ff00]'Add an empty Row[/COLOR]
         [COLOR=#0000ff]Let[/COLOR] y = y + 1[COLOR=#00ff00] ' Ensure that the next y goes to the next empty Row[/COLOR]
         [COLOR=#0000ff]Let[/COLOR] FinalRow = FinalRow + 1 ' Increase the loop stop value to account for the new Row
            Application.StatusBar = "Loop Count is " & LoopCount & ".   Row is " & y & ".  Final Row is " & FinalRow & "."
        [COLOR=#0000ff] Loop[/COLOR]
   MsgBox ("Done!")
   Cells.Columns(1).Clear
Err:
Application.StatusBar =[COLOR=#0000ff] False[/COLOR]
[COLOR=#0000ff]End Sub [/COLOR][COLOR=#00ff00]'DoWhileLoopInsertExtrarowAndChangeLoo[/COLOR]pEnd()
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
It is a cardinal sin of programming to change the loop index variable inside the loop, and the terminal value is determined on entry to the loop, so changes are ignored.

When you need that functionality, use a Do loop.
 
Upvote 0
shg said:
It is a cardinal sin of programming to change the loop index variable inside the loop, and the terminal value is determined on entry to the loop, so changes are ignored.

When you need that functionality, use a Do loop.
Click to expand...


. Thanks for the quick Reply. (Came quicker than it took me to make a Coffee!!).
. Yeah, as I mentioned I noticed that the Do Loop “worked”/ Functioned as I wanted it to.
. And it was good to have confirmed as I expected that the terminal value is determined on entry to the loop, so changes are ignored, (only for the Next Loop I think????) - I said I could not find it written anywhere, so maybe not a lot of people know that.
. As for my cardinal sin (changing the loop index variable inside the loop): I’ll use it for now as I can’t see another way. But I’m very grateful for the profi advice: As I know now I’m doing something bad (probably because it is dangerous, with a catastrophe waiting around the corner), I’ll keep looking for something better! .
Alan
 
Upvote 0
shg said:
It is a cardinal sin of programming to change the loop index variable inside the loop, and the terminal value is determined on entry to the loop, so changes are ignored.

When you need that functionality, use a Do loop.
Click to expand...

Alternately, if you wanted to use a For..Next loop, then create a MaxLoop counter, initialize it to what you are setting the loop's upper limit to, subtract one from the MaxLoop counter everytime you actually want to reduce the loop's upper limit by one, then at the bottom of the loop (just above the Next statement), execute this line of code...

If I > MaxLoop Then Exit For
 
Upvote 0
Rick Rothstein said:
Alternately, if you wanted to use a For..Next loop, then create a MaxLoop counter, initialize it to what you are setting the loop's upper limit to, subtract one from the MaxLoop counter everytime you actually want to reduce the loop's upper limit by one, then at the bottom of the loop (just above the Next statement), execute this line of code...

If I > MaxLoop Then Exit For
Click to expand...

. That does the trick, Super, Thanks. ( I am actually adding Rows to a file, so the Row Maximum gets bigger. But a variation on your idea is, I think, just about what I am looking for…. To illustrate here is a further test code that does what I want)

Code: 
 '..................
   [B][COLOR=#0000ff]Option Explicit[/COLOR] [/B][COLOR=#00ff00]' Be careful this time - Playing with Big Numbers!![/COLOR]

'..................
[COLOR=#0000ff]Sub[/COLOR][COLOR=#000000] For[/COLOR]Next4InsertExtrarowAndChangeLoopEndRickRothstein() [COLOR=#00ff00]'Way of Exiting Correctly For Next loop when the terminal value is changed in the loop[/COLOR]
   [COLOR=#0000ff]On Error GoTo[/COLOR] Err:
   Di[COLOR=#0000ff]m J[/COLOR] [COLOR=#0000ff][B]As Double[/B][/COLOR], FinalRow[COLOR=#0000ff] As Integer[/COLOR], LoopCount [COLOR=#0000ff]As Integer[/COLOR], MaxLoopBigNumber [B][COLOR=#0000ff]As Double[/COLOR][/B]
   [COLOR=#0000ff]Let[/COLOR] LoopCount = 0
   L[COLOR=#0000ff]et Fi[/COLOR]nalRow = 10
  [COLOR=#0000ff] Let[/COLOR] MaxLoopBigNumber = 200000 ' [COLOR=#00ff00]maybe my Cardinal Sin is a bit less?: At least I should [/COLOR][B][COLOR=#0000ff]stop[/COLOR][/B][COLOR=#00ff00] somewhere if anything goes wrong with loop index variable!![/COLOR]
       [COLOR=#0000ff] For[/COLOR] J = 1 [COLOR=#0000ff]To [/COLOR][COLOR=#000000]MaxLoop[/COLOR][COLOR=#0000ff][/COLOR]BigNumber [B][COLOR=#0000cd]Step[/COLOR][/B] 1 
        [COLOR=#0000cd]If[/COLOR] J > FinalRow [COLOR=#0000cd]Then Exit For[/COLOR] ' Trick to stop loop at modified FinalRow as "terminal value is determined on entry to the loop, so changes are ignored."
       [COLOR=#0000cd] Let L[/COLOR]oopCount = LoopCount + 1
        Cells(J, 1).Value = LoopCount
        Rows(J).Resize(1).Insert 'Add an empty Row
        Le[COLOR=#0000cd]t J [/COLOR]= J + 1 ' Ensure that the next y goes to the next empty Row
        Le[COLOR=#0000cd]t Fi[/COLOR]nalRow = FinalRow + 1 ' Increase the loop stop value to account for the new Row
            Application.StatusBar = "Loop Count is " & LoopCount & ".   Row is " & y & ".  Final Row is " & FinalRow & "."
        [COLOR=#0000cd]If J[/COLOR] > FinalRow [COLOR=#0000cd]Then Exit For [/COLOR]' Trick to stop loop at modified FinalRow as "terminal value is determined on entry to the loop, so changes are ignored."
        [COLOR=#0000cd]Next [/COLOR]J
   MsgBox ("Done!")
   Cells.Columns(1).Clear
Err:
Application.StatusBar = False
 
[COLOR=#0000cd]End Sub [/COLOR][COLOR=#00ff00]'ForNext4InsertExtrarowAndChangeLoopEndRickRothstein()[/COLOR]


. It still maybe bad to change the loop index variable inside the loop, but maybe the sin is not too bad here?!.
Danke, Thanks again
Alan
Bavaria
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
260
John_w
J
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
310
Minh Tuan VN
M
L
  • Question Question
Prevent Pagebreak from hidden columns and rows, combine visible columns to one page wide
Replies
1
Views
229
bobsan42
bobsan42
K
  • Question Question
Can you please explain why this code getting error while running
Replies
2
Views
225
Joe4
Joe4
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
47
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top