VBA 一个find方法的增强函数
‘本函数是一个find方法的增强函数,结合FindNext和FindPrevious方法,可以返回一组符合条件的单元格的集合; ‘本函数返回一个Collection对象,里面储存了找到的单元格; ‘本函数有两个必选参数: ‘ 1.SearchRange 用来存放需要查找的区域; ‘ 2.FindWhat用来存放需要查找的值; ‘其余参数都是可选参数,与Find方法参数相同; ‘无论是否查找到单元格,都会返回一个collection对象; ‘我们用Collection.Count=0,来判断,没有找到匹配的单元格; Option Explicit Function 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 Collection Dim 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 Then Else 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 If Set FindPlus = FoundCol ‘把集合对象赋值给函数名 End Function
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。