CODEWITHSUNDEEP
arraysexcelvba
Jarom
Jarom

Reputation: 1077

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

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:

1
1
1
2
2
2
2
2
3
3
4
5
6
6
7

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

Upvotes: 1

Views: 75

Answers (1)

Paras Parmar
Paras Parmar

Reputation: 343

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

Upvotes: 1

Related Questions