For loop question
 

Good morning or evening! I want to change the iLastRow when the rows are inserted. However, the loop ends when i = 22, even though the iLastRow is actually changed to 29 for example. Please advise me what is wrong here. Thanks

iLastRow = 2

For i = iRow To iLastRo
Set oCell = ws.Cells(i, iCol
Set nextCell = oCell.Offset(1, 0
If nextCell.Value < "" The
iDiff = DateDiff("m", oCell.Value, nextCell.Value
If iDiff 1 The
Set rngInsert = ws.Range(Cells(i + 1, iCol), Cells(i + iDiff - 1, iLastCol)
rngInsert.Selec
Selection.Insert Shift:=xlDow
'i + iDiff -
ws.Cells(i + 1, iCol).Value = DateAdd("m", 1, oCell
'Fill dow
ws.Range(Cells(i, iCol), Cells(i + 1, iCol)).Selec
Selection.AutoFill Destination:=ws.Range(Cells(i, iCol), Cells(i + iDiff - 1, iCol)), Type:=xlFillDefaul

ws.Range(Cells(i + 1, iLastCol), Cells(i + iDiff - 1, iLastCol)).Value =
'Increase
i = i + iDif
iLastRow = WorksheetFunction.CountA(ws.Columns(iCol)
End I
End I
Next i

For loop question
 

The looping conditions are set at the start of the loop in a for i = irow to
ilastrow type loop.

Use another type loop


i = iRow
do while i <= iLastrow

' code that sets iLastRow to new value

i = i + 1
Loop

or

i = iRow
do

' code that sets iLastRow to new value

i = i + 1
Loop until i iLastrow

--
Regards,
Tom Ogilvy

"luvgreen" wrote in message
...
Good morning or evening! I want to change the iLastRow when the rows are
inserted. However, the loop ends when i = 22, even though the iLastRow is
actually changed to 29 for example. Please advise me what is wrong here.
Thanks.

iLastRow = 22

For i = iRow To iLastRow
Set oCell = ws.Cells(i, iCol)
Set nextCell = oCell.Offset(1, 0)
If nextCell.Value < "" Then
iDiff = DateDiff("m", oCell.Value, nextCell.Value)
If iDiff 1 Then
Set rngInsert = ws.Range(Cells(i + 1, iCol), Cells(i + iDiff - 1,
iLastCol))
rngInsert.Select
Selection.Insert Shift:=xlDown
'i + iDiff - 1
ws.Cells(i + 1, iCol).Value = DateAdd("m", 1, oCell)
'Fill down
ws.Range(Cells(i, iCol), Cells(i + 1, iCol)).Select
Selection.AutoFill Destination:=ws.Range(Cells(i, iCol), Cells(i +
iDiff - 1, iCol)), Type:=xlFillDefault

ws.Range(Cells(i + 1, iLastCol), Cells(i + iDiff - 1,
iLastCol)).Value = 0
'Increase i
i = i + iDiff
iLastRow = WorksheetFunction.CountA(ws.Columns(iCol))
End If
End If
Next i