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

Question

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.

Just loop the array rows, and return the first row when matched. — Scott Craner
– Scott Craner, Commented Dec 16, 2019 at 16:47
You'd need to slice your 2D array to provide "the second column of a_array". — Mathieu Guindon
– Mathieu Guindon, Commented Dec 16, 2019 at 17:29

Scott Craner · Accepted Answer · 2019-12-16 16:53:30Z

1

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

answered Dec 16, 2019 at 16:53

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SJR · Accepted Answer · 2019-12-16 17:48:10Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related