I have a For loop that has an embedded If Statement that inserts X rows. The
problem is my For loop is based on the number of rows with data and once I
insert rows that number changes. How can I make the For loop parameters
update before each iteration? Here's the structure of my code:

LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Start = 1
NewRow = 6
'Outer loop for entire worksheet.
For RowCount = Start To LastRow
If .Range("A" & RowCount) < .Range("A" & (RowCount + 1)) Then
'If name changes make sure the rep has 5 or more transactions
StartRow = RowCount + 1
RowCount = RowCount + 5
CheckRow = StartRow
AddRow = 4
'If rep has at least 5 transactions then copy the first 5 and
'move them to the Tally Sheet
If .Range("A" & StartRow) = .Range("A" & RowCount) Then
.Range("A" & StartRow & ":F" & RowCount).Copy _
Destination:=Sheets("Tally Sheet").Range("A" & NewRow)
.Range("G" & StartRow & ":Q" & RowCount).Copy _
Destination:=Sheets("Tally Sheet").Range("N" & NewRow)
NewRow = NewRow + 8
Else
For counter = CheckRow To RowCount
If .Range("A" & CheckRow) = .Range("A" & (CheckRow + 1))
Then
AddRow = AddRow - 1
CheckRow = CheckRow + 1
Else
--------------- .Rows(CheckRow + 1).Resize(AddRow).Insert (xlShiftDown)
.Range("A" & StartRow & ":F" & RowCount).Copy _
Destination:=Sheets("Tally Sheet").Range("A" & NewRow)
.Range("G" & StartRow & ":Q" & RowCount).Copy _
Destination:=Sheets("Tally Sheet").Range("N" & NewRow)
NewRow = NewRow + 8
Exit For
End If
Next counter
End If
End If
Next RowCount
End With

At this line: .Rows(CheckRow + 1).Resize(AddRow).Insert (xlShiftDown) I
insert rows so that any rep that doesn't have 5 transactions does now. But
here's the problem: Say LastRow = 10 so my For loop initially sets up to run
from 1 to 10. Now say around row 5 I have to insert 3 extra rows. Now my
LastRow is actually 13 not 10 but my For loop doesn't update LastRow with
each iteration so it stops at row 10 leaving 3 rows at the end that the code
never sees. How do I correct this?

Hi

I would skip the For...Next loop, and use a Do...Loop Until look and
increase LastRow when rows are added (not tested):

Dim RowCount As Long
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Start = 1
NewRow = 6
'Outer loop for entire worksheet.
'For RowCount = Start To LastRow

Do
RowCount = RowCount + 1
If .Range("A" & RowCount) < .Range("A" & (RowCount + 1))
Then
'If name changes make sure the rep has 5 or more
transactions
StartRow = RowCount + 1
RowCount = RowCount + 5
CheckRow = StartRow
AddRow = 4
'If rep has at least 5 transactions then copy the first 5
and
'move them to the Tally Sheet
If .Range("A" & StartRow) = .Range("A" & RowCount) Then
.Range("A" & StartRow & ":F" & RowCount).Copy _
Destination:=Sheets("Tally Sheet").Range("A" &
NewRow)
.Range("G" & StartRow & ":Q" & RowCount).Copy _
Destination:=Sheets("Tally Sheet").Range("N" &
NewRow)
NewRow = NewRow + 8
Else
For Counter = CheckRow To RowCount
If .Range("A" & CheckRow) = .Range("A" & (CheckRow
+ 1)) Then
AddRow = AddRow - 1
CheckRow = CheckRow + 1
Else
.Rows(CheckRow + 1).Resize(AddRow).Insert
(xlShiftDown)
.Range("A" & StartRow & ":F" & RowCount).Copy
_
Destination:=Sheets("Tally Sheet").Range("A" &
NewRow)
.Range("G" & StartRow & ":Q" & RowCount).Copy
_
Destination:=Sheets("Tally Sheet").Range("N" &
NewRow)
NewRow = NewRow + 8
LastRow = LastRow + AddRow
Exit For
End If
Next Counter
End If
End If
'Next RowCount
Loop Until RowCount = LastRow
End With

Regards,
Per







On 21 Apr., 22:24, Bishop wrote:
I have a For loop that has an embedded If Statement that inserts X rows. *The
problem is my For loop is based on the number of rows with data and once I
insert rows that number changes. *How can I make the For loop parameters
update before each iteration? *Here's the structure of my code:

LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Start = 1
NewRow = 6
'Outer loop for entire worksheet. *
* * * For RowCount = Start To LastRow
* * * * *If .Range("A" & RowCount) < .Range("A" & (RowCount + 1)) Then
* * * * * * 'If name changes make sure the rep has 5 or more transactions
* * * * * * StartRow = RowCount + 1
* * * * * * RowCount = RowCount + 5
* * * * * * CheckRow = StartRow
* * * * * * AddRow = 4
* * * * * * 'If rep has at least 5 transactions then copy the first 5 and
* * * * * * 'move them to the Tally Sheet
* * * * * * If .Range("A" & StartRow) = .Range("A" & RowCount) Then
* * * * * * * * .Range("A" & StartRow & ":F" & RowCount).Copy _
* * * * * * * * * *Destination:=Sheets("Tally Sheet").Range("A" & NewRow)
* * * * * * * * .Range("G" & StartRow & ":Q" & RowCount).Copy _
* * * * * * * * * *Destination:=Sheets("Tally Sheet").Range("N" & NewRow)
* * * * * * * * NewRow = NewRow + 8
* * * * * * Else
* * * * * * * * For counter = CheckRow To RowCount
* * * * * * * * * * If .Range("A" & CheckRow) = .Range("A" & (CheckRow + 1))
Then
* * * * * * * * * * * * AddRow = AddRow - 1
* * * * * * * * * * * * CheckRow = CheckRow + 1
* * * * * * * * * * Else
--------------- * .Rows(CheckRow + 1).Resize(AddRow).Insert (xlShiftDown)
* * * * * * * * * * * * .Range("A" & StartRow & ":F" & RowCount).Copy _
* * * * * * * * * * * * Destination:=Sheets("Tally Sheet").Range("A" & NewRow)
* * * * * * * * * * * * .Range("G" & StartRow & ":Q" & RowCount).Copy _
* * * * * * * * * * * * Destination:=Sheets("Tally Sheet").Range("N" & NewRow)
* * * * * * * * * * * * NewRow = NewRow + 8
* * * * * * * * * * * * Exit For
* * * * * * * * * * End If
* * * * * * * * Next counter
* * * * * * End If
* * * * End If
* * * Next RowCount
* *End With

At this line: .Rows(CheckRow + 1).Resize(AddRow).Insert (xlShiftDown) I
insert rows so that any rep that doesn't have 5 transactions does now. *But
here's the problem: *Say LastRow = 10 so my For loop initially sets up to run
from 1 to 10. *Now say around row 5 I have to insert 3 extra rows. *Now my
LastRow is actually 13 not 10 but my For loop doesn't update LastRow with
each iteration so it stops at row 10 leaving 3 rows at the end that the code
never sees. *How do I correct this?