星期二, 十一月 01, 2011

Filter/Find in Array

From ExcelExperts.com
Filter/Find in Array:

Vishesh's picture


The following piece of code can be run from any general module. This code extracts all array elements based on the search criteria. You can also search the entire array element or part of it depending on the 3rd parameter of the function if its set to True or False.

Sub TestRun()
Dim arr
Dim varSearch
'-------------Variable parameters----------
arr = Array(1, 2, 3, 10, 110, 111)
varSearch = 1
'==========================================
MsgBox Join(FilterArray(arr, varSearch, True), vbCr)
End Sub
Function FilterArray(arrVariant, varSearch, Optional blnEntireElement As Boolean = True)
If blnEntireElement Then
FilterArray = FilterArrayExact(arrVariant, varSearch)
Else
FilterArray = Filter(arrVariant, varSearch, True)
End If
End Function
Function FilterArrayExact(arrVariant, varSearch)
Dim arrSearch As Variant
Dim arrFiltered As Variant
Const strEncl As String = ":;"
Const strDelim As String = ",,"
'first filter the array for elements containing search variant
arrFiltered = Filter(arrVariant, varSearch)
If UBound(arrFiltered) > -1 Then
'mark the beginning and end of each array element found
'strEncl and strDelim should not be in the array
arrSearch = Split(strEncl & Join(arrFiltered, strEncl & _
strDelim & strEncl) & strEncl, strDelim)
'again filter array, include Encl in search
arrFiltered = Filter(arrSearch, _
strEncl & varSearch & strEncl)
'clear Encl from final result
FilterArrayExact = Split(Replace(Join(arrFiltered, _
strDelim), strEncl, ""), strDelim)
End If
End Function


没有评论: