That turned out to be a harder request than I first thought it would be to
do (but fun to do, nonetheless<g). Just look at the last If..Then statement
to see what I mean... it identifies all rows up to the row before the
ActiveCell (accounting for the situation where the ActiveCell is on Row 1)
and makes a Union of them coupled with all cells starting in Column A up to
and including the ActiveCell's Column, and then Intersects that Union with
the found cell to make sure then don't overlap). I marked a spot in the code
where you can take action if you want to alert the user that there are no
more cells to be found after the ActiveCell (with, perhaps, a MessageBox I
would guess).

Sub FindNext_NoXXX()
Dim C As Range
Set C = Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If Not C Is Nothing Then
If Intersect(C, Union(Rows("1:" & (ActiveCell.Row + _
(ActiveCell.Row < 1))), Range("A" & _
ActiveCell.Row & ":" & ActiveCell.Address))) _
Is Nothing Then
C.Select
Else
'
' No more cells to be found... put any code you want here
' if you want to notify the user of that fact in some way
'
End If
End If
End Sub

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Thanks for your help, but I think I need to refine my question.

What I need is not to cycle through each occurrence of NoXXX but instead I
need to go to the first occurrence of NoXXX, after the active cell, and
select the cell containing NoXXX. If there is no additional occurrence of
NoXXX I want the macro to stop.

I do not want it to start looking above the active cell.



"OssieMac" wrote in message
...
I am assuming that you want the find in a loop so that it can find all
occurrences. If so then the following is adapted from an example in Help.
Note that I have specified a range to look in and specified After:=
.cells(.cells.count). The reason for this is to ensure that the code
initially looks after the last cell in the range which because it loops
around to the top is actually the first cell of the range. The Findnext
then
finds the subsequent occurrences (if any).

Sub testFind()

Dim c As Range
Dim firstAddress As String

With Worksheets("Sheet1").Range("a1:a500")
Set c = .Find(What:="NoXXX", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c.Address

Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address < firstAddress
End If
End With

End Sub

In lieu of the line
With Worksheets("Sheet1").Range("a1:a500")

you can use the following that includes all of the range that has been
used
on the worksheet.
With Worksheets("Sheet1").UsedRange

--
Regards,

OssieMac


"Pats" wrote:

Is there any way to cause this routine to stop when it finds the last
occurance of NoXXX and not start over at the top of the worksheet?

Sub NextRow()
'
' Macro4 Macro
'

'
Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

End Sub