# Problem changing For Loop Maximum inside the loop

#### Mackeral

##### Board Regular
Consider the following code:
Code:
``````    Last = 10
For I = 1 To Last
Last = Last + 1
Next I``````
At the end of the code you find I = 11 and Last = 21.

The implication of this is that once you set the Maximum for the loop, you can't change it.

So you have to use a Do While loop explicitly testing for the changed Maximum.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### JoeMo

##### MrExcel MVP
Not sure what your question is, but at the end of the looping Last = 20, not 21.

Code:
``````Sub test()
Last = 10
For i = 1 To Last
Last = Last + 1
Next i
MsgBox Last & vbCrLf & i
End Sub``````

#### Rick Rothstein

##### MrExcel MVP
The implication of this is that once you set the Maximum for the loop, you can't change it.
Correct... the loop limits are set immediately before the loop begins and cannot be changed by anything you do within the loop.

#### Mackeral

##### Board Regular
Yes, this is what I discovered. From my point of view (and something I find that often happens to me when I've been working with a piece of code over a long time), I understand how it should work in a way that I hadn't considered when I first wrote it. But it certainly took a long time to actually figure out what was going on in code that I expected to work since it was in the system programming. There is nothing I have ever encountered before that had this limit hard wired and un-documented feature before.

Last edited:

#### Mackeral

##### Board Regular
One other comment:
Don't forget to explicitly update the control variable which was handled by the "For Loop".

Last edited:

#### RoryA

##### MrExcel MVP, Moderator
There is nothing I have ever encountered before that had this limit hard wired and un-documented feature before.
It's actually clearly documented in the language spec.

1,102,843
Messages
5,489,216
Members
407,679
Latest member
Elaine Grass

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...