CODEWITHSUNDEEP
arraysvbaexcelsearchoptimization
Sai Nikhil
Sai Nikhil

Reputation: 1237

Search for the next instance in array

For i = 1 To max
    matchFoundIndex = Application.Match(arr(i), arr, 0)
Next

The above code returns the first occurrence of arr(i) in arr. However, there can be other instances of arr(i) in arr. In short, how can I efficiently find such next instances of arr(i) in arr (avoiding the classic n^2 loop)?

Upvotes: 1

Views: 211

Answers (1)

DisplayName
DisplayName

Reputation: 13386

you could "hide" every match found and keep using Application.Match():

Function GetIndexes(arr As Variant) As String
    Dim tempArr As Variant, matchIndex As Variant, element As Variant
    Dim matchIndexes As String

    tempArr = arr ' use a temporary array not to spoil the passed one
    For Each element In tempArr
        If element <> "|||" Then 'skip elements already marked as "already found"
            matchIndexes = ""
            matchIndex = Application.Match(element, tempArr, 0) 'search for array element matching current one
            Do
                matchIndexes = matchIndexes & matchIndex & " "
                tempArr(matchIndex - 1) = "|||" 'mark found array element as "already found"
                matchIndex = Application.Match(element, tempArr, 0) 'search for next array element matching current one
            Loop While Not IsError(matchIndex) ' loop until no occurrences of current array element
            GetIndexes = GetIndexes & "element '" & element & "' found at indexes: " & Replace(Trim(matchIndexes), " ", ",") & vbCrLf
        End If
    Next
End Function

which you could exploit as follows:

Sub main()
    Dim i As Long
    Dim arr As Variant

    arr = Array("a1", "a2", "a3", "a1", "a2", "a3")

    MsgBox GetIndexes(arr)

End Sub

Upvotes: 1

Related Questions