带有 For 循环的多维数组 VBA

尝试检查第一列中的值（即多维数组中的列），如果它匹配，则对另一列中与该行匹配的值进行排序。

我认为我做错了，但这是我第一次搞乱多维数组。

我是否需要在每个 for 循环中使用 UBound 和 LBound 来告诉它要查看哪一列？

除了当前问题的答案/解决方案之外，我绝对有兴趣学习将来使用它的最佳实践方法。

Code:

 Private Sub ThisStuff()

 Dim CoaAmt As Long
 Dim COAArray(3, 2)
 Dim ThisValue As String
 Dim AnotherValue As String

 AnotherValue = "Bananas"
 ThisValue = "Apples"


 COAArray(0, 0) = "Apples"
 COAArray(1, 0) = "Oranges"
 COAArray(2, 0) = "Peaches"
 COAArray(3, 0) = "Pomegranets"
 COAArray(0, 1) = 498
 COAArray(0, 1) = 505
 COAArray(1, 1) = 564
 COAArray(1, 2) = 556
 COAArray(2, 1) = 570
 COAArray(2, 2) = 573
 COAArray(3, 1) = 742
 COAArray(3, 2) = 750


 If AnotherValue = "Bananas" Then
     For i = COAArray(0, 0) To COAArray(3, 0)
             For j = COAArray(1, 0) To COAArray(3, 2)
                 If COAArray(i, j) = ThisValue Then CoaAmt = COAArray(i, j)
             Next j
     Next i
 End If

 MsgBox ("The value of CoaAmt is    " & CoaAmt)

 End Sub

是的。这LBound https://msdn.microsoft.com/en-us/library/t9a7w1ac(v=vs.90).aspx and UBound https://msdn.microsoft.com/en-us/library/office/gg278658.aspx函数允许您指定rank。这使您的嵌套 For .. Next 循环可以循环遍历所有数组元素。

 debug.print LBound(COAArray, 1) & ":" & UBound(COAArray, 1)
 debug.print LBound(COAArray, 2) & ":" & UBound(COAArray, 2)
 If AnotherValue = "Bananas" Then
     For i = LBound(COAArray, 1) To UBound(COAArray, 1)
         For j = LBound(COAArray, 2) To UBound(COAArray, 2)
             If COAArray(i, j) = ThisValue Then CoaAmt = COAArray(i, j)
         Next j
     Next i
 End If

您的数组元素分配有点混乱。它应该更接近，

COAArray(0, 0) = "Apples"
COAArray(1, 0) = "Oranges"
COAArray(2, 0) = "Peaches"
COAArray(3, 0) = "Pomegranates"
COAArray(0, 1) = 498
COAArray(1, 1) = 505
COAArray(2, 1) = 564
COAArray(3, 1) = 556
COAArray(0, 2) = 570
COAArray(1, 2) = 573
COAArray(2, 2) = 742
COAArray(3, 2) = 750

例如，通过上面修复的数组分配，COA Array(0, 0) 是 Apple 的，COA Array(0, 1) 是 498，COAArray(0, 2) 是 570。下面吐出 498 和 570。

    Dim i As Long, j As Long
    Dim COAArray(3, 2) As Variant, CoaAmt(0 To 1) As Variant

    Dim ThisValue As String, AnotherValue As String

    AnotherValue = "Bananas"
    ThisValue = "Apples"

    COAArray(0, 0) = "Apples"
    COAArray(1, 0) = "Oranges"
    COAArray(2, 0) = "Peaches"
    COAArray(3, 0) = "Pomegranets"
    COAArray(0, 1) = 498
    COAArray(1, 1) = 505
    COAArray(2, 1) = 564
    COAArray(3, 1) = 556
    COAArray(0, 2) = 570
    COAArray(1, 2) = 573
    COAArray(2, 2) = 742
    COAArray(3, 2) = 750

    If AnotherValue = "Bananas" Then
        For i = LBound(COAArray, 1) To UBound(COAArray, 1)
            If COAArray(i, 0) = ThisValue Then
                For j = LBound(COAArray, 2) + 1 To UBound(COAArray, 2)
                   CoaAmt(j - 1) = COAArray(i, j)
                Next j
            End If
        Next i
    End If

    MsgBox "The value of CoaAmt is  " & CoaAmt(LBound(CoaAmt)) & "  " & CoaAmt(UBound(CoaAmt))

我不得不改变你的CoaAmtvar 到一维变体数组，以便收集两个数字并输出它们。