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

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Valeria" wrote:

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

for i =1 to 10 step 5
'Actions
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Valeria" wrote in message
...
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

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...
--
HTH...

Jim Thomlinson


"Valeria" wrote:

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

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


"Valeria" wrote in message
...
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

Thank you very much to all of you!!!!!! I keep learning useful things here.
Kind regards
--
Valeria


"Bernie Deitrick" wrote:

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


"Valeria" wrote in message
...
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

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

On Nov 29, 12:44 pm, Valeria
wrote:
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