Avoid Using Pivot Table - VBA Code Review

[movbanewbie]

Hi.
I have data that has three lines of data for each unique id. I have code I inherited that loops through data and pastes in another cell. The problem, I can't figure out how to amend the code to sum the unique ids. I don't want to use a pivot table due to formatting and range adjustment.

Snap shot of some of the data I am trying to sum and organize:
Global ID Currency Start date Maturity Nominal EOD MV Accrual
0
1123334 USD 29/07/2011 29/07/2013 1,000,000 -1,652.02
1123334 USD 29/07/2011 29/07/2013 -2,152.62
1123335 USD 29/07/2011 29/07/2013 368.6072222
1123335 USD 29/07/2011 29/07/2013 17,000,000 -28,084.41
1123335 USD 29/07/2011 29/07/2013 -36,594.58



MDate As Date
Term As Double
NPV As Double
FlatDelta As Double
SwaptionVega As Variant
SprdDelta As Double
End Type

Sub Read_Blotter(numtrades() As TraderView)
Dim i As Integer, mosec As Integer

i = 5
mosec = 0
Sheets("Pricing").Select
Do While (Cells(i, 2).Value <> "")
mosec = mosec + 1
ReDim Preserve numtrades(1 To mosec)
numtrades(mosec).RiskGroup = Cells(i, 14).Value
numtrades(mosec).TradeID = Cells(i, 2).Value
numtrades(mosec).TradeType = Cells(i, 4).Value
numtrades(mosec).Fees = Cells(i, 5).Value
numtrades(mosec).Cpty = Cells(i, 9).Value
numtrades(mosec).Notional = Cells(i, 10).Value
numtrades(mosec).EDate = Cells(i, 11).Value
numtrades(mosec).MDate = Cells(i, 12).Value
numtrades(mosec).Term = Cells(i, 13).Value
numtrades(mosec).NPV = Cells(i, 22).Value
numtrades(mosec).FlatDelta = Cells(i, 23).Value
numtrades(mosec).SwaptionVega = Cells(i, 24).Value
numtrades(mosec).SprdDelta = Cells(i, 25).Value
i = i + 1
Loop
End Sub

Sub Print_Blotter(numtrades() As TraderView)
Dim i As Integer
Sheets("TraderView").Select
On Error Resume Next
If UBound(numtrades) = 0 Then
Exit Sub
End If
On Error GoTo 0
For i = 1 To UBound(numtrades)
Cells(i + 6, 1).Value = numtrades(i).RiskGroup
Cells(i + 6, 2).Value = numtrades(i).TradeID
Cells(i + 6, 3).Value = numtrades(i).TradeType
Cells(i + 6, 4).Value = numtrades(i).Cpty
Cells(i + 6, 5).Value = numtrades(i).Notional
Cells(i + 6, 6).Value = numtrades(i).EDate
Cells(i + 6, 7).Value = numtrades(i).MDate
Cells(i + 6, 8).Value = numtrades(i).Term
Cells(i + 6, 9).Value = numtrades(i).NPV
Cells(i + 6, 10).Value = numtrades(i).Fees
Cells(i + 6, 11).Value = numtrades(i).FlatDelta
Cells(i + 6, 12).Value = numtrades(i).SwaptionVega
Cells(i + 6, 13).Value = numtrades(i).SprdDelta
Next i
End Sub

 

[SkipVought]

Hi,

You're making this entirely too complex!

What version of Excel?

This can ALL be done with native Excel features, no VBA. You might want to master the process and then turn on your macro recorder.

1) Create a unique list from the first 4 columns of your data using either Advanced Filter, 2007 Remove Duplicates or MS Query.

2) Use multiple criteria aggregation formulas like SUMIFS or SUMPRODUCT.

The entire thing could be done is less than 15 minutes, most likely!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}