loop for-next with variable end

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Valeria - 29 Nov 2007 17:44 GMT

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

Reply to this Message

sebastienm - 29 Nov 2007 17:49 GMT

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

Signature

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

> Dear experts,
> I need to perform a long loop with many actions on a numer of rows; these
[quoted text clipped - 16 lines]
>
> Valeria

Reply to this Message

Don Guillett - 29 Nov 2007 17:50 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Dear experts,
> I need to perform a long loop with many actions on a numer of rows; these
[quoted text clipped - 18 lines]
>
> Valeria

Reply to this Message

Jim Thomlinson - 29 Nov 2007 17:55 GMT

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

Signature

HTH...

Jim Thomlinson

> Dear experts,
> I need to perform a long loop with many actions on a numer of rows; these
[quoted text clipped - 16 lines]
>
> Valeria

Reply to this Message

Bernie Deitrick - 29 Nov 2007 18:02 GMT

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

> Dear experts,
> I need to perform a long loop with many actions on a numer of rows; these
[quoted text clipped - 16 lines]
>
> Valeria

Reply to this Message

Valeria - 30 Nov 2007 08:25 GMT

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

Signature

Valeria

> Valeria,
>
[quoted text clipped - 30 lines]
> >
> > Valeria

Reply to this Message

ilia - 30 Nov 2007 16:14 GMT

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 <Vale...@discussions.microsoft.com>
wrote:

> Dear experts,
> I need to perform a long loop with many actions on a numer of rows; these
[quoted text clipped - 16 lines]
>
> Valeria

Reply to this Message