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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1