CODEWITHSUNDEEP
arraysvbaindexingmatch
davisjpn
davisjpn

Reputation: 3

Using Match and Index function with an array created and populated in VBA

I created and defined an array in VBA: 

A_Array(2,4) As Variant (Option Base 1)

I want to match numbers in the 2nd column against specific criteria, for example, which row in the second column contains the number "1". Once I find the match then I want to use the corresponding value in the first column to create a variable. How do I specify the range, in this case an entire column of a VBA created array, when i use the index and match functions? 

Variable = Worksheet.Function.Index(A_Array, Worksheet.Function.Match(1, **?Second_ Column of A_Array?**,0),**?First Column of A_Array?**)

How do i specify the first column of A_Array inside the Match function above and how do I specify the second column of the A_Array inside the Index function.

Thanks in advance for any help.

Upvotes: 0

Views: 2390

Answers (2)

SJR
SJR

Reputation: 23081

If you are wanting to find an array element equal to 1, this is the slicing approach which doesn't require a loop.

Most of this code is populating an array and showing it on a sheet for the purposes of illustrating the results, so you probably won't need any of that.

Sub x()

Dim A_Array(1 To 4, 1 To 2) As Variant, i As Long, variable As Variant, v As Variant

For i = LBound(A_Array, 1) To UBound(A_Array, 1) 'just populating array with any old stuff so you won't need
    A_Array(i, 1) = i * 2
    A_Array(i, 2) = i * 3
Next i

A_Array(2, 2) = 1   'make sure something in 2nd column is 1

Range("A1").Resize(4, 2).Value = A_Array

v = Application.Match(1, Application.Index(A_Array, , 2), 0) 'returns position of 1 in second column (or error if no match)

if isnumeric(v) then variable = A_Array(v, 1) 'find corresponding element in 1st column
Range("D1").Value = variable

End Sub

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

Just loop the rows and test, with variant arrays it will be quick.

Dim A_Array(2, 4) As Variant
'fill array

Dim i As Long
For i = LBound(A_Array, 1) To UBound(A_Array, 1)
    If A_Array(i, 2) = 1 Then
        variable = A_Array(i, 1)
        Exit For
    End If
Next i

Upvotes: 1

Related Questions