CODEWITHSUNDEEP
excelvbaexcel-2019
usmanharoon
usmanharoon

Reputation: 195

Get the reverse index of a value from Array in VBA

I have an integer array.

Dim a as Variant
a = Array(1,2,3,4,1,2,3,4,5)
Dim index as Integer
index = Application.Match(4,a,0) '3

index is 3 here. It returns the index of first occurrence of 4. But I want the last occurrence index of 4.

In python, there is rindex which returns the reverse index. I am new to vba, any similar api available in VBA? Note: I am using Microsoft office Professional 2019 Thanks in advance.

Upvotes: 0

Views: 857

Answers (3)

FaneDuru
FaneDuru

Reputation: 42236

Try the next way, please:

Sub lastOccurrenceMatch()
   Dim a As Variant, index As Integer
    a = Array(1, 2, 3, 4, 1, 2, 3, 4, 5)

    index = Application.Match(CStr(4), Split(StrReverse(Join(a, "|")), "|"), 0) '2
    Debug.Print index, UBound(a) + 1 - index + 1
End Sub

Or a version not raising an error in case of no match:

Sub lastOccurrenceMatchBetter()
   Dim a As Variant, index As Variant
    a = Array(1, 2, 3, 4, 1, 2, 3, 4, 5)

    index = Application.Match(CStr(4), Split(StrReverse(Join(a, "|")), "|"), 0) '2
    If Not IsError(index) Then
        Debug.Print index, UBound(a) + 1 - index + 1
    Else
        Debug.Print "No any match..."
    End If
End Sub

Edited:

The next version reverses the array as it is (without join/split sequence). Just using Index. Just for the sake of playing with arrays:

Sub testMatchReverseArray()
Dim a As Variant, index As Integer, cnt As Long, col As String, arrRev()
    a = Array(1, 2, 3, 4, 1, 12, 3, 4, 15)
    cnt = UBound(a) + 1
    col = Split(cells(1, cnt).Address, "$")(1)
    arrRev = Evaluate(cnt & "+1-column(A:" & col & ")") 'build the reversed columns array

    a = Application.index(Application.Transpose(a), arrRev, 0)

    Debug.Print Join(a, "|") 'just to visually see the reversed array...
    index = Application.match(4, a, 0)
    Debug.Print index, UBound(a) + 1 - index + 1
End Sub

Second Edit:

The next solution matches the initial array with one loaded with only element to be searched. Then reverse it (using StrReverse) and search the matching positions ("1"):

Sub MatchLastOccurrence()
   Dim a(), arr(), srcVar, arrMtch(), index As Integer
   
   a = Array(1, 2, 3, 4, 1, 12, 3, 4, 15)
   srcVar = 4 'the array element to identify its last position
   
   arr = Array(srcVar)    
   arrMtch = Application.IfError(Application.match(a, arr, 0), 0)
    Debug.Print Join(arrMtch, "|") '1 for matching positions, zero for not matching ones
    
    index = Application.match("1", Split(StrReverse(Join(arrMtch, "|")), "|"), 0) '2
    Debug.Print index, UBound(a) + 1 - index + 1
End Sub

Upvotes: 3

T.M.
T.M.

Reputation: 9948

Alternative via FilterXML()

Just in order to complete the valid solutions above, I demonstrate another approach via FilterXML() (available since vers. 2013+):

This method doesn't require to reverse the base array; instead it filters & counts all elements before the last finding (i.e. all elements that have another right neighbor with the searched value).

Function lastPos(arr, ByVal srch) As Long
'a) create wellformed xml string & XPath search string
    Dim xml:   xml = "<all><i>" & Join(arr, "</i><i>") & "</i></all>"
    Dim XPath: XPath = "//i[following-sibling::i[.=" & srch & "]]"
'b) Filter xml elements before finding at last position
    With Application
        Dim x: x = .FilterXML(xml, XPath)  ' << apply FilterXML()
        If IsError(x) Then                 ' no finding or 1st position = srch
            lastPos = IIf(arr(0) = srch, 1, .Count(x))
        Else
            lastPos = .Count(x) + 1        ' ordinal position number (i.e. +1)
        End If
    End With
End Function

Example call

Dim arr:  arr  = Array(1, 2, 3, 4, 1, 2, 3, 4, 5)
Dim srch: srch = 4
Debug.Print _
    "Last Position of " & srch & ": " & _
    lastPos(arr, srch)   ' ~~> last Pos of 4: 8

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36880

XMATCH() avaibalble in O365 and Excel 2021. Try-

Sub ReverseMatch()
Dim a As Variant
Dim index As Integer

    a = Array(1, 2, 3, 4, 1, 2, 3, 4, 5)
    index = Application.XMatch(4, a, 0, -1) '-1 indicate search last to first.
    Debug.Print index

End Sub

You can also try below sub.

Sub ReverseMatch()
Dim a As Variant
Dim index As Integer, i As Integer

    a = Array(1, 2, 3, 4, 1, 2, 3, 4, 5)

    For i = UBound(a) To LBound(a) Step -1
        If a(i) = 4 Then
            Debug.Print i + 1
            Exit For
        End If
    Next i

End Sub

Upvotes: 3

Related Questions