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

I think you want the `LBound` and `UBound` methods which returns the lower/upper boundary of an array: `For i = LBound(COAArray) To UBound(COAArray)` — SierraOscar, Feb 15 '16 at 17:13

score 4 · Accepted Answer · 2016-02-15T17:43:09.907

4

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.

edited Feb 15 '16 at 17:43

answered Feb 15 '16 at 17:15

why do we need debug.print "etc etc" on both lines? – Doug Coats Feb 15 '16 at 17:17
1

I thought you were having trouble with a two-dimensioned array's **rank**. The 1 and 2 are the rank positions. I put that in to demonstrate the proper use of rank. It returns `0:3` and `0:2` in that order. – Feb 15 '16 at 17:23
I think i am having issues more with trying to properly sort through a multidimensional array. I ended up getting a type mismatch error, but thats because studio thinks the value of COAArray is equal to apples. I guess I am thinking about the multidimensional array incorrectly. I was thinking of it in spreadsheet terms(i.e. find value in column a and find the corresponding row value from column). Maybe an array isnt the right direction for this? – Doug Coats Feb 15 '16 at 17:31
I hate to sound utterly stupid but can you explain what "CoaAmt(j - 1) =" is used for? I want to understand the moving parts so I wont have future questions on this issue. Also, what if my second dimension has more than two values? How would I find a value between them? – Doug Coats Feb 15 '16 at 18:51
1

No worries. As mentioned in my preamble to the example, you want to cycle through `COAArray(i, 0)` until it is **Apples**. Then you want to put `COAArray(i, 1)` and `COAArray(i, 2)` into CoaAmt, a standard 1-D array. Since the available position in CoaAmt are `CoaAmt(0)` and `CoaAmt(1)` you have to start **j** at 1 to get the correct position in the second rank of COAArray and use **j-1** for the correct position in CoaAmt. – Feb 16 '16 at 01:39
So, just curious, if the value of "this value" isnt known before hand, i obv have to use variant, but then i would have to make the "CoaAmt(j - 1)" some version of j-variant, right? – Doug Coats Feb 17 '16 at 12:22
1

No, it sounds like it would be the same. Might help to have real data with expected results to visualize the situation. – Feb 17 '16 at 12:31
Fair enough. I dont have that, I am thinking more conceptually at the moment so that I know how to fits any situation. BUt it does make sense b/c j would be the already set at that level i think. – Doug Coats Feb 17 '16 at 13:06

Multidimensional Arrays with For Loops VBA

1 Answers1

Linked