Lartk
11-05-2012, 09:00 AM
On the attached workbook, in the "Now" sheet represents my output now. I use this macro to divide up each cusip:
Sub ProcessData4()
Dim lastrow As Long
Dim endat As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 5 Step -1
endat = i
Do While .Cells(i - 1, "C").Value = .Cells(i, "C").Value And i - 1 >= 5
i = i - 1
Loop
.Rows(endat + 1).Insert
.Cells(endat + 1, "A") = "Total Quantity:"
.Cells(endat + 1, "I").FormulaR1C1 = "=SUM(R[-1]C:R" & i & "C)"
.Rows(i).Resize(2).Insert
.Cells(i + 1, "A").Value = "Trade Allocation:"
Next i
End With
End Sub
However, as you can see, the cusips are separated by I would also like the buys and sells grouped for each cusip. So can I take the above macro one step further and have it not only group each cusip separately but also group the buy and sell (in column E, B=buy, s = sell) for each cusip? My desired results are on the "Results" sheet.
Sub ProcessData4()
Dim lastrow As Long
Dim endat As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 5 Step -1
endat = i
Do While .Cells(i - 1, "C").Value = .Cells(i, "C").Value And i - 1 >= 5
i = i - 1
Loop
.Rows(endat + 1).Insert
.Cells(endat + 1, "A") = "Total Quantity:"
.Cells(endat + 1, "I").FormulaR1C1 = "=SUM(R[-1]C:R" & i & "C)"
.Rows(i).Resize(2).Insert
.Cells(i + 1, "A").Value = "Trade Allocation:"
Next i
End With
End Sub
However, as you can see, the cusips are separated by I would also like the buys and sells grouped for each cusip. So can I take the above macro one step further and have it not only group each cusip separately but also group the buy and sell (in column E, B=buy, s = sell) for each cusip? My desired results are on the "Results" sheet.