0

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.

Improve this question
0

3 Answers 3

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I am using Microsoft Office Professional 2019. Getting "Object doesn't support this property" error
Excel-2019 doesn't have XMATCH() function. Its only available to Excel-365.
@usmanharoon You can try my edited codes.
Helpful & instructive to see a use of XMatch +:) Side note: I wouldn't declare index As Long or Integer, but as Variant to allow a check like If Not IsError(index) or even more speaking If IsNumeric(index) @Harun24h
@T.M. Thanks. If there possibility to not found the lookup value then ISERROR would be good choice.
|
3

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
Improve this answer

7 Comments

Nice, but the StrReverse command will not work for numbers like 10 or 12, where shifting places would change the value.
@Olle Sjögren Yup... I know it. I observed that the array in discussion contains only 1 digit elements...:) It is the single case (I think) to be correctly reversed without iteration. Otherwise, an iteration will be necessary...
@Olle Sjögren Please, see the updated answer, the part after Editede. Just for the sake of playing with arrays...
Just for the sake of the art, I posted another approach via FilterXML to complete all the valid solutions here +:)
@T.M. Yes, you are right! I knew that the arrays to be matched do not have to be of the same size, but I did not think at an array with a single element, even if I should. And I overcomplicated the code because of that... Thanks! I will also adapt the code with the simpler/more compact way. But playing with array is interesting, anyhow. :)
|
1

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
Improve this answer

3 Comments

Interesting, as usually... Voted up. I was also thinking of obtaining an array of the searched number (4, in this case) with the same number of elements as the array to be searched for (arr), of course, without iteration. To obtain such an array of strings "4" is simple, but do you have an idea of transforming its elements in numbers. I remember that you did the reverse operation. I mean obtaining an array of strings using Evaluate("ROW(...)"). I do not remember how you did it, anymore... Do you remember? I like you to refresh my memory. You added somehow the null string at the end.
Maybe misunderstanding you: Did you mean how to get the reverse order of 1 to 9 by evaluation resulting e.g. in Array(9,8,7,6,5,4,3,2,1)? - Having defined the elements count cnt = 9 and a virtual column character col = Split((Columns(cnt).Address(, 0)), ":")(0) you might try arr = Evaluate(cnt & "+1-column(A:" & col & ")") @FaneDuru
I forgot that in my answer I promised to find a way of building the reversed columns array, necessary to reverse the array to be processed, using Application.index... You 'answered' that part... :) Thanks! I was really busy and forgot about it. I was asking for something like Array(4, 4, 4, 4, 4, 4, 4, 4, 4), in case of matching last occurrence of 4, in order to post a different way of answering to the original question. I found a way in the meantime. I will adapt the previous code, using the built arran and post another way, too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.