首页 > 代码库 > 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方法的增强函数
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。