K
kirkm
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
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