krishnak
09-27-2022, 05:18 PM
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
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