Return an Array of Indexes for Matching Values in a Primary Array

Question

I have an array with multiple duplicates. I'm trying to write code that will produce a second array that contains the indexes of all of the elements in the first array that equal a look up value.

For example, I have this array:

I want a second array that will return the indexes of the number 6.

This is the code that I have right now.

Sub test()
Dim look_up As Integer
Dim id_ar As Variant
Dim index_ar As Variant


look_up = 6
id_ar = Range("A1:A16").Value
index_ar = Application.Match(id_ar, look_up, True)

End Sub

I want this to result in an array like this:

13
14

But it just returns a bunch of '#N/A's

Without having tried anything, I notice something. Dim look_up As Integer followed by an integer named lookup instead of look_up — Uvar
– Uvar, Commented Jul 7, 2017 at 13:31
@Uvar whoops, somehow I must have changed that when I copied it over. The variable names match up in the code I'm running. I edited the question to fix that. Thank you. — Jarom
– Jarom, Commented Jul 7, 2017 at 13:39
Not to keep beating the same problem, but your Application.Match also mentions lookup instead of look_up. Is that correct in the code as well? — Busse
– Busse, Commented Jul 7, 2017 at 13:48
@Busse yes, that is correct in my code. Thanks for seeing that as well. — Jarom
– Jarom, Commented Jul 7, 2017 at 13:51

Paras Parmar · Accepted Answer · 2017-07-07 14:05:28Z

1

Some extremely simple code may help you better than brief and concise approaches.

Definitions : arrOut is your desired output, rngLookup is where your number 6 and any others reside. rngDB is where you want to search for the indexes of the lookup values.

Option Explicit

Sub dupes()
Dim rngLookup, rngDB As Range
Dim i, j As Variant
Dim arrOut As New Collection

Set rngLookup = Worksheets("Sheet1").Range("B1")
Set rngDB = Worksheets("Sheet1").Range("A1:A14")


Dim counter As Integer
counter = 0

For Each i In rngLookup

    For Each j In rngDB
        counter = counter + 1
        If j.Value = i.Value Then
            arrOut.Add Item:=CInt(counter)
        Else
        End If
    Next j
Next i

End Sub

answered Jul 7, 2017 at 14:05

Paras Parmar

3432 silver badges10 bronze badges

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

4 Comments

Paras Parmar Over a year ago

If you need an array output. Use this code from Brett's page.link

Jarom Over a year ago

I'm a little worried about using a loop because I want the code to be able to perform quickly with scale. I'm not sure how long it would take if the collection had 100,000 elements. But maybe this is the best way to solve the problem in VBA.

Paras Parmar Over a year ago

Hi Jarom, rest assured, the Collection data structure is a proven and reliable piece of VBA engineering. I used to be skeptical of looping till I realized that the hot path was the array at the centre of the 2 loops. Any assignment, redim and preserve at the hot path would drive an application's running time higher and higher. The collection mitigates that and can be used to scale up very quickly. If you understand how to use a collection, I'd whole heartedly recommend that one junks the array entirely.

Jarom Over a year ago

I didn't realize this. Thank you for clarifying. The code you provided works perfectly. I don't think I'll be using arrays for VBA anymore based on your rational.

Collectives™ on Stack Overflow

Return an Array of Indexes for Matching Values in a Primary Array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related