I’ve tried to find a solution to this online and for whatever reason simply cannot locate anything (perhaps my search skills are lacking). I have what I feel is a fairly simple query.
I am declaring an array and assigning data to it using the following, which I have confirmed is working:
Dim arr As Variant arr = Sheet.Range("A1").CurrentRegion
Now, given that this is a 2-dimensional array (only 2 columns of data), I want to then use a match-type of function to return the row number associated with a value in column 1 of the array. My end goal is to then use that to call the corresponding value in column 2 of the array – essentially building an index/match type of scenario but in VBA. However, the Application.Match function fails as this isn’t a 1-dimensional array. See example below:
1|Apple |Fruit 2|Tomato |Fruit 3|Carrot |Vegetable 4|Zucchini|Vegetable
What I am trying to achieve is something like MATCH(“Tomato”, arr(), 0) to get the result of “2” being the row number associate with Tomato. I only need to do my search in column 1 of the array. Does anyone perhaps have any suggestions on the easiest way to do this?
Thanks a mil!