Multidimensional Arrays with For Loops VBA

Question

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.

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.

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

Answer 1

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

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))

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