I have a procedure that sorts a list of reps by their id and then transaction
amount. Then copies the top 5 transactions to another sheet. The problem is
when a rep has less than 5 transactions it throws off the other spreadsheet
from that point on. I need something that checks to see if the rep has 5
transactions and if he doesn't it tacks on the appropriate amount of "dummy"
transactions to make a total of 5 and then copies those 5 transactions to the
other sheet. How can I do this? Here's the code I have so far:

Sub TallySheetRepDump()
Call BanSumSort
NewRow = 6
With Sheets("Catalyst Dump")
'The following line of code calculates the number of rows of data
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
'Sort by UID (column A) then by Transaction Amount (column F)
.Rows("2:" & LastRow).Sort _
Key1:=.Range("A2"), _
Order1:=xlAscending, _
Key2:=.Range("F2"), _
Order2:=xlAscending, _
Header:=xlNo

Start = 1
NewRow = 6
'Outer loop for entire worksheet. There is an error here if the
'rep doesn't have 5 or more adjustments in Catalyst Dump
For RowCount = Start To LastRow
'Check to see if RowCount is equal to the next row. If not that
'means the name has changed and we want to capture the info for
'the current rep
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
'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
'this is where I need something that inserts "dummy"
transactions
'to give the rep a total of 5 and then copy to the other sheet
End If
End If
Next RowCount
End With

Hi

Try this:

....
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 = 1 To 4
If .Range("A" & StartRow) < .Range("A" & counter)
Then
counter = counter - 1
.Range("A" & StartRow & ":F" & counter).Copy _
Destination:=Sheets("Tally Sheet").Range
("A" & NewRow)
.Range("G" & StartRow & ":Q" & counter).Copy _
Destination:=Sheets("Tally Sheet").Range
("N" & NewRow)
NewRow = NewRow + 8
Exit For
End If
Next
End If

Hopes this helps:

---
Per

On 21 Apr., 18:53, Bishop wrote:
I have a procedure that sorts a list of reps by their id and then transaction
amount. *Then copies the top 5 transactions to another sheet. *The problem is
when a rep has less than 5 transactions it throws off the other spreadsheet
from that point on. *I need something that checks to see if the rep has 5
transactions and if he doesn't it tacks on the appropriate amount of "dummy"
transactions to make a total of 5 and then copies those 5 transactions to the
other sheet. *How can I do this? *Here's the code I have so far:

Sub TallySheetRepDump()
* *Call BanSumSort
* *NewRow = 6
* *With Sheets("Catalyst Dump")
* * * 'The following line of code calculates the number of rows of data
* * * LastRow = .Range("B" & Rows.Count).End(xlUp).Row
* * * 'Sort by UID (column A) then by Transaction Amount (column F)
* * * .Rows("2:" & LastRow).Sort _
* * * * *Key1:=.Range("A2"), _
* * * * *Order1:=xlAscending, _
* * * * *Key2:=.Range("F2"), _
* * * * *Order2:=xlAscending, _
* * * * *Header:=xlNo

* * * Start = 1
* * * NewRow = 6
* * * 'Outer loop for entire worksheet. *There is an error here if the
* * * 'rep doesn't have 5 or more adjustments in Catalyst Dump
* * * For RowCount = Start To LastRow
* * * * *'Check to see if RowCount is equal to the next row. *If not that
* * * * *'means the name has changed and we want to capture the info for
* * * * *'the current rep
* * * * *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
* * * * * * '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
* * * * * * * * 'this is where I need something that inserts "dummy"
transactions
* * * * * * * * 'to give the rep a total of 5 and then copy to the other sheet
* * * * * * End If * * * *
* * * * End If
* * * Next RowCount
* *End With

For counter = 1 To 4
If .Range("A" & StartRow) < .Range("A" & counter) Then

If StartRow = 5 and I need to compare A5 with A6 that just compares A5 with A1

"Per Jessen" wrote:

Hi

Try this:

....
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 = 1 To 4
If .Range("A" & StartRow) < .Range("A" & counter)
Then
counter = counter - 1
.Range("A" & StartRow & ":F" & counter).Copy _
Destination:=Sheets("Tally Sheet").Range
("A" & NewRow)
.Range("G" & StartRow & ":Q" & counter).Copy _
Destination:=Sheets("Tally Sheet").Range
("N" & NewRow)
NewRow = NewRow + 8
Exit For
End If
Next
End If

Hopes this helps:

---
Per

On 21 Apr., 18:53, Bishop wrote:
I have a procedure that sorts a list of reps by their id and then transaction
amount. Then copies the top 5 transactions to another sheet. The problem is
when a rep has less than 5 transactions it throws off the other spreadsheet
from that point on. I need something that checks to see if the rep has 5
transactions and if he doesn't it tacks on the appropriate amount of "dummy"
transactions to make a total of 5 and then copies those 5 transactions to the
other sheet. How can I do this? Here's the code I have so far:

Sub TallySheetRepDump()
Call BanSumSort
NewRow = 6
With Sheets("Catalyst Dump")
'The following line of code calculates the number of rows of data
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
'Sort by UID (column A) then by Transaction Amount (column F)
.Rows("2:" & LastRow).Sort _
Key1:=.Range("A2"), _
Order1:=xlAscending, _
Key2:=.Range("F2"), _
Order2:=xlAscending, _
Header:=xlNo

Start = 1
NewRow = 6
'Outer loop for entire worksheet. There is an error here if the
'rep doesn't have 5 or more adjustments in Catalyst Dump
For RowCount = Start To LastRow
'Check to see if RowCount is equal to the next row. If not that
'means the name has changed and we want to capture the info for
'the current rep
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
'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
'this is where I need something that inserts "dummy"
transactions
'to give the rep a total of 5 and then copy to the other sheet
End If
End If
Next RowCount
End With