VB 查找數組中所有匹配項並返回下標集合

VB/VBA 中需查找數組中所有等於目標值的元素下標，返回完整下標列表。原生無此函數，需自行實現。

實現函數：返回匹配下標的集合

Function FindAllIndexes(arr As Variant, searchValue As Variant) As Collection
  Dim result As New Collection
  Dim i As Long
  
  For i = LBound(arr) To UBound(arr)
    If arr(i) = searchValue Then
      result.Add i, CStr(i)  ' 用下標作鍵，便於後續判重
    End If
  Next i
  
  Set FindAllIndexes = result
End Function

使用示例

Dim data(1 To 10) As Integer
data(1) = 5: data(2) = 3: data(3) = 5: data(4) = 7: data(5) = 5

Dim matches As Collection
Set matches = FindAllIndexes(data, 5)

Dim idx As Variant
For Each idx In matches
  Debug.Print "找到下標: " & idx  ' 輸出 1, 3, 5
Next

' 若需返回數組而非集合
Dim indexArray() As Long
ReDim indexArray(1 To matches.Count)
Dim j As Long
For Each idx In matches
  j = j + 1
  indexArray(j) = idx
Next
' indexArray 現在包含 (1, 3, 5)

二維數組查找整行匹配

若數組為二維 arr(1 To n, 1 To m)，需比較整行：

Function FindMatchingRows(arr As Variant, targetRow As Variant) As Collection
  Dim result As New Collection
  Dim i As Long, j As Long, match As Boolean
  
  For i = LBound(arr, 1) To UBound(arr, 1)
    match = True
    For j = LBound(arr, 2) To UBound(arr, 2)
      If arr(i, j) <> targetRow(j) Then
        match = False
        Exit For
      End If
    Next j
    If match Then result.Add i, CStr(i)
  Next i
  
  Set FindMatchingRows = result
End Function

參考資料

百度知道相關討論