lundi 1 octobre 2018

VBA: If - ElseIf with multiple Or conditions

I am looping trough column 2 in sheet Summary Report, looking for a value <> 0.

Summary Report sheet

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