老程序:查找包含指定内容的所有单元格

2023-12-14 14:36:01 浏览数 (3)

学习Excel技术,关注微信公众号:

excelperfect

标签:VBA,自定义函数

我们知道,Find方法只是返回找到的第1个单元格,如果查找区域有多个符合查找内容的单元格,Find方法就无能为力了。然而,利用Find方法可以查找并返回包含指定内容的所有单元格。在完美Excel中曾多次介绍过这样的程序。恰巧,今天逛网站时又碰到一个这样的程序,特将其拿过来,粘贴在此,供有兴趣的朋友参考。

VBA代码如下:

代码语言:javascript复制
Function Find_Range(Find_Item As Variant, _
 Search_Range As Range, _
 Optional LookIn As Variant, _
 Optional LookAt As Variant, _
 Optional MatchCase As Boolean) As Variant
 
 Dim c As Range
 Dim CustArry() As Variant
 Dim row As Integer
 Dim firstAddress As String
 
 If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
 If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
 If IsMissing(MatchCase) Then MatchCase = False
 
 With Search_Range
   Set c = .Find( _
     What:=Find_Item, _
     LookIn:=LookIn, _
     LookAt:=LookAt, _
     SearchOrder:=xlByRows, _
     SearchDirection:=xlNext, _
     MatchCase:=MatchCase, _
     SearchFormat:=False)
   If Not c Is Nothing Then
     Set Find_Range = c
     firstAddress = c.Address
     Do
       Set Find_Range = Union(Find_Range, c)
       Set c = .FindNext(c)
     Loop While Not c Is Nothing And c.Address <> firstAddress
   End If
 End With
End Function

欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。

0 人点赞