Hi VBA Experts!
I coded to extract and load tables for each instrument ( Cash, Stock, Bond, Option, CDS) showing its value as well as the total portfolio value for the same date.(highlighted in Blue)
Now, I need to make 5 different scenario tables for each instrument, filling the yellow highlighted areas as in the below screen shot for each Scenario.
For example, Scenario 1 is the combination of Stock, Bond, Option and CDS (but, No Cash), which equals to the total Total Portfolio minus cash.
Capture4.PNG
I want to use the remove method in Collection and Select, Case statement as below. But, the remove statement in the Sub TestGetPortfolioValue() below doesn't work
Please share with me if there is better way to do this.
(I put the range reference as Sceario1, Scenario2, Scenario3, Scenario4, Scenario 5 where I would like load the data in a spreadsheet.)
.
Select Case
Case is newPort.Der.Remove "cash"
For i = 1 To 10
Range("scenario1").Cells(i, 1) = newPort.GetTotal(Range("Scenario1").Cells(i, 1))
Next i
Case is newPort.Der.Remove "Stock"
For i = 1 To 10
Range("scenario2").Cells(i, 1) = newPort.GetTotal(Range("Scenario2").Cells(i, 1))
Next i
Case is newPort.Der.Remove "Bond"
For i = 1 To 10
Range("scenario3").Cells(i, 1) = newPort.GetTotal(Range("Scenario3").Cells(i, 1))
Next i
Case is newPort.Der.Remove "Option"
For i = 1 To 10
Range("scenario4").Cells(i, 1) = newPort.GetTotal(Range("Scenario4").Cells(i, 1))
Next i
Case is newPort.Der.Remove "CDS"
For i = 1 To 10
Range("scenario5").Cells(i, 1) = newPort.GetTotal(Range("Scenario5").Cells(i, 1))
Next i
End Select
Anyone of you can enlighten me?
Any help is greatly appreciated.
Regards,
Demian