loop for-next with variable end

  • Thread starter Thread starter Valeria
  • Start date Start date
V

Valeria

Dear experts,
I need to perform a long loop with many actions on a numer of rows; these
actions may result in incrementing the # of rows, and I had used a for -
next loop but it does not work, as the end value always stays the same as in
the beginning, even if the corresponding variable gets changed during the
loop. To give you an example
a=1
b=10
For i = a to b
'ACTIONS
b=b+5
Next i

This does not change b, it will always stay 10, but in my case I need it to
get changed...
Can somebody please help me?
Thanks!
Kind regards

Valeria
 
Hi,
I suppose you are adding rows after the current row being processed which
pushes the upper boundary each time.
Instead, start by the bottom row and process going up.

a=1
b=10
For i = b to a Step -1
'ACTIONS
Next i
 
You can not change the boundries of a loop within the loop itself and it is
considered to be VERY bad practice to change the counter (i in your case)
within the loop itself. Either determine the size of the loop ahead of time
or use a do while or do until loop...
 
Valeria,

It sounds like you are inserting rows - in which case, it is better to step upwards:

a = 1
b = 10
for i = b to a step -1
'Actions that insert rows?
Next i

HTH,
Bernie
MS Excel MVP
 
Thank you very much to all of you!!!!!! I keep learning useful things here.
Kind regards
 
Public Sub mySub()
Dim a As Integer, b As Integer
Dim i As Integer

Const step As Integer = 1

a = 1
b = 10

i = a
Do While i < b
' actions
i = i + step
b = b + 5
Loop
End Sub
 
Back
Top