Thread: Help with .Find
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
kirkm[_8_] kirkm[_8_] is offline
external usenet poster
 
Posts: 166
Default Help with .Find


Hi,

This (for me) complex code does work -

x = FindIt("23","A1:A20")

I intend this to look through cells A1 to A20 and
if any have a '23', add that row number to the array.

--
Function FindIt(ByVal What, Where, Optional SearchC)

If IsMissing(SearchC) Then SearchC = xlWhole

Dim rngFound As Range
Dim rngFred As Range
Dim strFirst As String
ReDim mArray(0)
'where = Worksheets("Sheet1").Range(where)

With Range(Where)
Set rngFound = .Find(What:=What, after:=.Range("A1"),
LookIn:=xlValues, LookAt:=SearchC, searchorder:=xlByColumns,
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)
If Not rngFound Is Nothing Then
strFirst = rngFound.Address
ReDim Preserve mArray(UBound(mArray) + 1)
mArray(UBound(mArray)) = rngFound.Row
Set rngFred = rngFound
Do
Set rngFound = .FindNext(rngFound)
If rngFound.Address < strFirst Then
Set rngFred = Union(rngFred, rngFound)
ReDim Preserve mArray(UBound(mArray) + 1)
mArray(UBound(mArray)) = rngFound.Row
End If
Loop Until rngFound.Address = strFirst
End If
End With
FindIt = mArray
End Function
--

But I'd like to specify a sheet by name and my remmed line
'where = Worksheets("Sheet1").Range(where)' causes an error.

Could someone please explain why/what the error is and also
what ' after:=.Range("A1")' means ? There's no help for this
and if it means start looking from A1, shouldn't that be
set by the passed range parameter ?

Thanks - Kirk