时间戳

首页 > 解决方案 > VBA：如果 - 具有多个或条件的 ElseIf

vba - VBA：如果 - 具有多个或条件的 ElseIf

问题描述

我正在循环工作表摘要报告中的第 2 列，寻找值 <> 0。

总结报告表

我正在使用嵌套If语句来确定第 1 列中的工具是债券还是商品或股票或外汇。如果第 1 列中第 2 列中值 <> 0 的符号对应于资产类别之一，并且该资产类别没有工作表，则应为该资产类别创建一个新工作表。

On Error Resume Next

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

当循环命中ElseIf语句中的资产时，第 2 列中的值 <> 0，它只是跳到End If并转到Next Iteration。

为什么？

标签： vbaexcelfor-loopif-statement

解决方案

我建议您使用 Collection/Dictionary 对象来保存列表。此示例使用字典（=hashmap，关联数组）作为示例。使用Tools/ReferenncesVBA 编辑器中的菜单启用脚本运行时。这是大多数体面大小的 VBA 应用程序需要的东西。

您可以在参数表中维护列表并在 VBA 函数中填充字典。我试图以合理的格式构建循环，以便更容易理解正在发生的事情。

大多数编程指南也有充分的理由避免使用 GOTO 命令。但是，VBA 缺少 continue 语句，如果您问我，这是 goto 语句的罕见用途。

Option Explicit
' Tools/References: [x]Microsoft Scripting Runtime

Public Sub doIt()
    Dim ws As Worksheet
    Dim iRow As Long
    Dim idx As Long
    Dim val As String
    Dim key As String
    Dim bonds As New Scripting.Dictionary
    Dim commodities As New Scripting.Dictionary

    Call bonds.Add("*GER10YBond*", "")
    Call bonds.Add("*Gilt10Y*", "")
    Call bonds.Add("*JPN10yBond*", "")
    Call bonds.Add("*US30YBond*", "")

    Call commodities.Add("*[#]Corn*", "")
    Call commodities.Add("*[#]NaturalGas*", "")
    Call commodities.Add("*[#]Oil*", "")
    Call commodities.Add("*[#]Wheat*", "")

    Set ws = Application.Sheets("Sheet1")
    For iRow = 3 To ws.UsedRange.Rows.Count
        val = ws.Cells(iRow, 2).Value
        If val = "0" Or val = "" Then GoTo ContinueLoop
        val = LCase(ws.Cells(iRow, 1).Value)

        For idx = 0 To bonds.Count - 1
            key = bonds.Keys(idx)
            If val Like LCase(key) Then
                ws.Cells(iRow, 3) = "bonds " & key
                GoTo ContinueLoop
            End If
        Next

        For idx = 0 To commodities.Count - 1
            key = commodities.Keys(idx)
            If val Like LCase(key) Then
                ws.Cells(iRow, 3) = "commodities " & key
                GoTo ContinueLoop
            End If
        Next

        ws.Cells(iRow, 3) = "Unknown"

ContinueLoop:
        ' next step
    Next iRow
End Sub

推荐阅读