|
|
来一个，非原创！！！
- '本函数是一个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
|
|
楼主: opiona
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2018-3-9 12:38
楼主
