從VBA中的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

這里的索引是 3。它回傳第一次出現的索引 4。但我想要最后一次出現的索引 4。

在 python 中，有 rindex 回傳反向索引。我是 vba 新手，VBA 中是否有類似的 api 可用？注意：我正在使用 Microsoft Office Professional 2019 提前致謝。

uj5u.com熱心網友回復：

XMATCH()在 O365 和 Excel 2021 中可用。嘗試-

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

你也可以試試下面的子。

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

uj5u.com熱心網友回復：

請嘗試下一個方法：

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

或者在不匹配的情況下不引發錯誤的版本：

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

編輯：

下一個版本按原樣反轉陣列（沒有加入/拆分序列）。只是使用Index. 只是為了玩陣列：

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

第二次編輯：

下一個解決方案將初始陣列與僅加載了要搜索的元素的陣列相匹配。然后反轉它（使用StrReverse）并搜索匹配的位置（“1”）：

Sub MatchLastOccurrence()
   Dim a(), arr(), srcVar, arrMtch(), colEnd As String, index As Integer
   
   a = Array(1, 2, 3, 4, 1, 12, 3, 4, 15)
   srcVar = 4 'the array element to identify its last position
   
   colEnd = Split(cells(1, UBound(a)   1).Address, "$")(1)
   arr = Evaluate("COLUMN(A:" & colEnd & ")-COLUMN(A:" & colEnd & ") " & srcVar)
    Debug.Print Join(arr, "|") 'just to visually present the array content
    
   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

uj5u.com熱心網友回復：

替代方式FilterXML()

只是為了完成上述有效的解決方案，我通過FilterXML()（自 2013 年以上版本可用）演示了另一種方法：

此方法不需要反轉基本陣列；相反，它過濾并計算最后一次發現之前的所有元素（即所有元素與搜索值具有另一個右鄰居）。

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

示例呼叫

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

標籤：擅长 vba excel-2019

上一篇：標記列中的單元格的選項，以便它可以用于復制某些行資料

下一篇：列范圍輸入為數字，因此可以更改