PDA

View Full Version : [SOLVED] the remove method in Collection and Select Case



demian
10-05-2017, 02:36 PM
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.
20584

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:rotlaugh:
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

Paul_Hossler
10-05-2017, 03:30 PM
1. Select Case works by using a 'value' (Newport.Der below) and matching it with a Case selector (Case Is "cash") and then executing the statements that are part of the selected Case

2. I don't know where or why you wanted the .Remove

3. If you want Stock + Bond + Option + CDS (no Cash) then you need to add those four

4. the Select Case is case-sensitive, so if the data is "Cash" then "cash" will not match





Select Case newPort.Der

Case Is "cash"
For i = 1 To 10
Range("scenario1").Cells(i, 1) = newPort.GetTotal(Range("Scenario1").Cells(i, 1))
Next i



...etc.





End Select

demian
10-05-2017, 07:47 PM
Many Thanks, Paul!:)