Expanding the range

[krishnak]

Hi All,
I need help to find out the problem in the following code. The purpose is to add a new row below the active cell, increment the number by 1 and stopping when the the row count reached 100.


Sub InsertNewRows()
Dim oCel As Range
Dim nRows As Integer, nVal As Integer
Dim i As Integer

Set oCel = ActiveSheet.Range("A9")
nRows = 4
For i = 1 To nRows

nVal = oCel.Value
Set oCel = oCel.Offset(1, 0)
oCel.EntireRow.Insert
Set oCel = oCel.Offset(-1, 0)
oCel.Value = nVal + 1
nRows = nRows + 1
If (nRows > 100) Then Exit For
Next i
End Sub


I expected the above code to run up to 100 to create new rows below and then adding numbers as required in the code. But the loop exits after creating only 4 rows, even though the upper limit is incremented to 8. When the value of i reaches 5 (but nRows value shows 8), the code exits.
Can someone please explain why is this happening? I am working on a bigger issue where the range size is increased by adding rows and the upper limit of the loop is automatically increased.
Thank you

[JKwan]

For one thing, remove

Nrow = nrow + 1

You are inside the loop
Also, nrow will never get to 100 because you set the loop to 4 as upper limit

[krishnak]

Thanks for the response, JKwan. I fail to see the logic why the upper limit will not reach 100.
I tested the code in the Debug mode.
After 4 iterations, the values of i and nRows at the code "For i = 1 To nRows" are 4 and 8 respectively. This means that the upper limit (nRows) is increased from 4 to 8. The value of i at the code "Next i" is 4. With the next step (by F8), the loop exits.
By removing the code "nRows = nRows +1 " does not help. This exits at the count of 4. That is logical because nRows is not incremented and only i is incremented.

[JKwan]

Sorry, misread your code. Your code is kind of confusing. I don't really understand what you are doing....
To explain


Nrows=4
For I = 1 to nrows
By that your loop is set to 4, full stop
That will explain your I ended up being 4 and nrow at 8 (nrow=nrow+1)
When I gets passed 4, you exit your for-next loop

[snb]

Sub M_snb()
With Sheet1.ListObjects(1)
For j = 1 To 120
.ListRows.Add .ListRows.Count + 1
If .ListRows.Count = 100 Then Exit For
Next
End With
End Sub

[krishnak]

Hello All... Thanks for the responses. Now I understand that in a "For.... Next" loop, the number of iterations is determined before the loop starts. While the loop is running, the change in the upper limit (nRows) does not have any effect on the number of iterations as it is pre-determined. I expected the incremental variable to compare its value with the upper limit at that moment for every iteration. That is not the case.
Thanks to all of you who responded to my thread.