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

Hi Kirk,

Lets answer the After question first. After is exactly what it says. Find
the first occurrence of 23 AFTER A1. Now if A1 contains the value 23 then it
will be found last because all of the other cells with 23 will be found in
order after A1 and then the find loops around to the first cell. If you want
to find 23 in A1 first then you need to tell the find to look after the last
cell in the range which when it loops around the next cell is the first cell.

Example (Note space and underscore at the end of a line is a line break in
an otherwise single line of code):-

Set rngFound = .Find(What:=What, after:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=SearchC, searchorder:=xlByColumns, _
searchdirection:=xlNext, MatchCase:=False, matchbyte:=False)

Second question.

There are several ways of achieving what you require.

Example 1:-
With Worksheets("Sheet1").Range(Where)

Example 2:-
Assign worksheet to a variable first and use the variable in lieu of the
worksheet name.
Dim wsht As Worksheet
Set wsht = Worksheets("Sheet1")
With wsht.Range(Where)

You can even include the worksheet name in the function call. Change the
Function to the following:-
Function FindIt(ShtName As String, ByVal What, Where, Optional SearchC)

Then call the function as follows:-
x = FindIt("Sheet1", "23", "A3:A22")


Hope this helps.

--
Regards,

OssieMac


"kirkm" wrote:


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

Hi, please find the modified code..An additional optional argument is added
to pass the sheet name. if missing it will take the active sheet. Please try
and get back

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

If IsMissing(SearchC) Then SearchC = xlWhole
If IsMissing(strSheet) Then strSheet = ActiveSheet.Name

Dim rngFound As Range
Dim rngFred As Range
Dim strFirst As String
Dim strLookAfter As String
ReDim mArray(0)

strLookAfter = Split(Where, ":")(0)

With Worksheets(strSheet).Range(Where)
Set rngFound = .Find(What:=What, after:=.Range(strLookAfter), _
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


If this post helps click Yes
---------------
Jacob Skaria


"kirkm" wrote:


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

Dear Ossie & Jacob,

Thank you both for the solutions and info/examples.
Very helpful and the routine is working perfectly..

Cheers - Kirk