How can I determine whether a value exists anywhere within a multidimensional array variable with multiple levels?
I am trying to write a loop that adds various numbers to a two-dimensional array. The loop needs to check whether, for each loop case, a value associated with this case is already present in the array (and if it is, then skip to the next case).
If I have a single dimension array, I can use the MATCH function to achieve this checking task:
Sub arrayfindtest()
Dim data()
Dim output As Variant
ReDim data(1, 3)
data(1, 1) = 1
data(1, 2) = 10
data(1, 3) = 3
output = WorksheetFunction.Match(1, data, 0)
Debug.Print output
End Sub
However, if I add another level to the first dimension of the array variable, this doesn't work (as MATCH obviously only searches in one column). So, this doesn't work:
Sub arrayfindtest()
Dim data()
Dim output As Variant
ReDim data(2, 3)
data(1, 1) = 1
data(1, 2) = 10
data(1, 3) = 3
output = WorksheetFunction.Match(1, data, 0)
Debug.Print output
End Sub
In my scenario, two types of solutions would work: either a solution that checks whether the value exists anywhere in the array variable, or a solution where I specify one particular dimension of the array variable, and check in that.
Any suggestions much appreciated.
Bookmarks