J
Joe in Australia via OfficeKB.com
I want a function which will return a range containing all the cells matching
certain criteria: like .find, but returning more than one cell at a time. I
tried this and I get really weird results - sometimes it skips cells,
sometimes it loops forever. Any suggestions?
'Takes the same arguments as the built-in .Find, plus an argument specifying
the range to work on.
Function FindRange(MyRange As Range, What As Variant, Optional After As
Variant, Optional LookIn As Variant, Optional LookAt As Variant, Optional
SearchOrder As Variant, Optional SearchDirection As XlSearchDirection,
Optional MatchCase As Variant, Optional Matchbyte As Variant, Optional
Searchformat As Variant) As Range
Dim TempFindRange As Excel.Range
Dim ResultRange As Excel.Range
Dim FirstAddress As String
Set ResultRange = MyRange.Find(What:=What, After:=After, LookIn:=LookIn,
LookAt:=LookAt, SearchOrder:=SearchOrder, SearchDirection:=SearchDirection,
MatchCase:=MatchCase, Matchbyte:=Matchbyte, Searchformat:=Searchformat)
Set TempFindRange = ResultRange
If Not ResultRange Is Nothing Then
FirstAddress = ResultRange.Address
Do
TempFindRange = Excel.Union(TempFindRange, ResultRange)
Set ResultRange = MyRange.FindNext(ResultRange)
Loop While (ResultRange.Address <> FirstAddress)
End If
Set FindRange = TempFindRange
End Function
certain criteria: like .find, but returning more than one cell at a time. I
tried this and I get really weird results - sometimes it skips cells,
sometimes it loops forever. Any suggestions?
'Takes the same arguments as the built-in .Find, plus an argument specifying
the range to work on.
Function FindRange(MyRange As Range, What As Variant, Optional After As
Variant, Optional LookIn As Variant, Optional LookAt As Variant, Optional
SearchOrder As Variant, Optional SearchDirection As XlSearchDirection,
Optional MatchCase As Variant, Optional Matchbyte As Variant, Optional
Searchformat As Variant) As Range
Dim TempFindRange As Excel.Range
Dim ResultRange As Excel.Range
Dim FirstAddress As String
Set ResultRange = MyRange.Find(What:=What, After:=After, LookIn:=LookIn,
LookAt:=LookAt, SearchOrder:=SearchOrder, SearchDirection:=SearchDirection,
MatchCase:=MatchCase, Matchbyte:=Matchbyte, Searchformat:=Searchformat)
Set TempFindRange = ResultRange
If Not ResultRange Is Nothing Then
FirstAddress = ResultRange.Address
Do
TempFindRange = Excel.Union(TempFindRange, ResultRange)
Set ResultRange = MyRange.FindNext(ResultRange)
Loop While (ResultRange.Address <> FirstAddress)
End If
Set FindRange = TempFindRange
End Function