首页 > 代码库 > VBA 一个find方法的增强函数

VBA 一个find方法的增强函数

本函数是一个find方法的增强函数,结合FindNext和FindPrevious方法,可以返回一组符合条件的单元格的集合;本函数返回一个Collection对象,里面储存了找到的单元格;本函数有两个必选参数:   1.SearchRange 用来存放需要查找的区域;   2.FindWhat用来存放需要查找的值;其余参数都是可选参数,与Find方法参数相同;无论是否查找到单元格,都会返回一个collection对象;我们用Collection.Count=0,来判断,没有找到匹配的单元格;Option ExplicitFunction FindPlus(SearchRange As Range, FindWhat As Variant, _                  Optional After As Range, _                  Optional LookIn As Variant = xlFormulas, _                  Optional LookAt As Variant = xlPart, _                  Optional SearchOrder As Variant = xlByRows, _                  Optional SearchDirection As Variant = xlNext, _                  Optional MatchCase As Variant = False, _                  Optional MatchByte As Variant = True, _                  Optional SearchFormat As Variant = False) As CollectionDim FoundCell As Range 存放找到的单元格区域;Dim AfterCell As Range 存放查找的起始单元格;Dim FoundCol As Collection 存放找到单元格区域的集合;Dim firstAddress As String 存放第一次找到的单元格的地址Set FoundCol = New Collection Collecion类实例化为对象下面这个判断语句完成对After参数值的控制If After Is Nothing ThenElse  Set AfterCell = After 如果after参数不为空,这用提供的after参数值End If查找第一个符合条件的值Set FoundCell = SearchRange.Find(what:=FindWhat, After:=AfterCell, _                              LookIn:=LookIn, _                              LookAt:=LookAt, _                              SearchOrder:=SearchOrder, _                              SearchDirection:=SearchDirection, _                              MatchCase:=MatchCase, _                              MatchByte:=MatchByte, _                              SearchFormat:=SearchFormat)If Not FoundCell Is Nothing Then    firstAddress = FoundCell.Address 如果找到第一个值,然后把单元格地址赋值给FirstAddress变量        下面的循环是在区域里不断查找需要的值,并不断添加到FoundCol集合    Do      FoundCol.Add FoundCell 把找到的单元格赋值给FoundCol对象            根据SearchDirection参数,判断是向上搜索,还是向下搜索      If SearchDirection = xlNext Then        Set FoundCell = SearchRange.FindNext(After:=FoundCell)      Else        Set FoundCell = SearchRange.FindPrevious(After:=FoundCell)      End If    Loop Until (FoundCell Is Nothing) Or (FoundCell.Address = firstAddress) 经典用法,只要找到单元格和第一个找到的单元格地址不一样,就一直循环End IfSet FindPlus = FoundCol 把集合对象赋值给函数名End Function

 

VBA 一个find方法的增强函数