I am looping trough column 2 in sheet Summary Report, looking for a value <> 0.
I am using a nested If
statement to determine if the instrument in column 1 is either Bond Or Commodity Or Stock Or FX. If the symbol from column 1 with value <> 0 in column 2 corresponds to one of the asset classes and there is no worksheet for that asset class, then it should create a new worksheet for that asset class.
For i = 3 To SR.Cells(SR.Rows.Count, 2).End(xlUp).Row
If (SR.Cells(i, 2).Value <> 0) And _
((SR.Cells(i, 1).Value Like "*GER10YBond*") Or _
(SR.Cells(i, 1).Value Like "*Gilt10Y*") Or _
(SR.Cells(i, 1).Value Like "*JPN10yBond*") Or _
(SR.Cells(i, 1).Value Like "*US30YBond*")) Then
'Create new Worksheet named "Bonds"
ElseIf (SR.Cells(i, 2).Value <> 0) And ((SR.Cells(i, 2).Value Like "*#Corn*") Or _
(SR.Cells(i, 2).Value Like "*#NaturalGas*") Or _
(SR.Cells(i, 2).Value Like "*#Oil*") Or (SR.Cells(i, 2).Value Like "*#Wheat*") Or _
(SR.Cells(i, 2).Value Like "*#XAGUSD*") Or (SR.Cells(i, 2).Value Like "*#XAUUSD*") Or _
(SR.Cells(i, 2).Value Like "*Aluminium*") Or (SR.Cells(i, 2).Value Like "*BrentOil*") Or _
(SR.Cells(i, 2).Value Like "*Cocoa*") Or (SR.Cells(i, 2).Value Like "*Cocoa!*") Or _
(SR.Cells(i, 2).Value Like "*Cocoa!*") Or (SR.Cells(i, 2).Value Like "*Coffee*") Or _
(SR.Cells(i, 2).Value Like "*Coffee!*") Or (SR.Cells(i, 2).Value Like "*Coffee!*") Or _
(SR.Cells(i, 2).Value Like "*Copper*") Or (SR.Cells(i, 2).Value Like "*Corn*") Or _
(SR.Cells(i, 2).Value Like "*Corn!*") Or (SR.Cells(i, 2).Value Like "*Corn!*") Or _
(SR.Cells(i, 2).Value Like "*Cotton*") Or (SR.Cells(i, 2).Value Like "*Cotton!*") Or _
(SR.Cells(i, 2).Value Like "*NaturalGas*") Or (SR.Cells(i, 2).Value Like "*Oil*") Or _
(SR.Cells(i, 2).Value Like "*Palladium*") Or (SR.Cells(i, 2).Value Like "*Platinum*") Or _
(SR.Cells(i, 2).Value Like "*Rice*") Or (SR.Cells(i, 2).Value Like "*soybeans*") Or _
(SR.Cells(i, 2).Value Like "*Soybeans!*") Or (SR.Cells(i, 2).Value Like "*Soybeans!*") Or _
(SR.Cells(i, 2).Value Like "*Soybeans!*") Or (SR.Cells(i, 2).Value Like "*Wheat*") Or _
(SR.Cells(i, 2).Value Like "*Wheat!*") Or (SR.Cells(i, 2).Value Like "*Wheat!*") Or _
(SR.Cells(i, 2).Value Like "*XAGUSD*") Or (SR.Cells(i, 2).Value Like "*XAGUSD.*") Or _
(SR.Cells(i, 2).Value Like "*XAUUSD*") Or (SR.Cells(i, 2).Value Like "*XAUUSD.*")) Then
' Create new Worksheet named "Commodities"
End If
Next i
When the loop hits an asset from the ElseIf
statement, with a value in column 2 <> 0, it just skips to End If
and goes to Next Iteration
.
Why?
Aucun commentaire:
Enregistrer un commentaire