具有循环VBA的多维数组 [英] Multidimensional Arrays with For Loops VBA

问题描述

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

Trying to check column one for a value (column in the multidimensional array that is) and if it matches sort another column for the value that matches that row.

我认为我这样做是错误的，但这是我第一次混乱多维数组。

I think I am doing this wrong, but this is the first time I am messing with multidimensional arrays.

我需要在每个for循环中使用UBound和LBound来告诉你要查看什么colum？

Would I need to use UBound and LBound in each for loop to tell it what colum to look through?

除了当前问题的答案/解决方案外，我对于学习未来使用此最佳做法的方法感兴趣。

I am def interested in learning the best practice method for using this in the future, aside from just an answer/solution tot he current issue.

代码：

 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 和
< a href =https://msdn.microsoft.com/en-us/library/office/gg278658.aspx =nofollow> UBound 功能可让您指定等级。这允许你嵌套For .. Next循环遍历所有数组元素。

Yes. The LBound and UBound functions allow you to specify the rank. This lets your nested For .. Next loops to cycle through all array elements.

 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

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

Your array element assignment was a little messed up. It should have been closer to,

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

$ b $例如，对于上面修改的数组分配，COAArray（0,0）是Apples，COAArray（0,1）是498，COAArray（0,2）是570.以下吐出498和570。

For example, with the repaired array assignment above, COAArray(0, 0) is Apples, COAArray(0, 1) is 498 and COAArray(0, 2) is 570. The following spits out 498 and 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))

我不得不更改你的 CoaAmt var到一维变体数组，以收集数字并输出它们。

I had to change your CoaAmt var to a one-dimensioned variant array in order to collect both numbers and output them.

这篇关于具有循环VBA的多维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！