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

See the example for the Find method... it shows you exactly how to set up
the loop. To get to the help files for it, put the text cursor on the word
Find and press F1.

--
Rick (MVP - Excel)


"Pats" wrote in message
...
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

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

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

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

Another way of doing this is instead of searching the entire worksheet is to
restrict the range being searched.

Set the range from the activecell to the end of the worksheet.

Sub FindAfterActivCell()

Dim c As Range
Dim rngToSearch As Range

Set rngToSearch = Range(ActiveCell, _
Cells(Rows.Count, Columns.Count))

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

If Not c Is Nothing Then
c.Activate
Else
'Your code here if not found
MsgBox "Not found"
End If

End Sub



--
Regards,

OssieMac


"Rick Rothstein" wrote:

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

That won't work all the time. Put a single NoXXX on the worksheet, in say
B18, and make the Active Cell, say, D15... you won't find the NoXXX because
it it in a column before the ActiveCell.

--
Rick (MVP - Excel)


"OssieMac" wrote in message
...
Another way of doing this is instead of searching the entire worksheet is
to
restrict the range being searched.

Set the range from the activecell to the end of the worksheet.

Sub FindAfterActivCell()

Dim c As Range
Dim rngToSearch As Range

Set rngToSearch = Range(ActiveCell, _
Cells(Rows.Count, Columns.Count))

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

If Not c Is Nothing Then
c.Activate
Else
'Your code here if not found
MsgBox "Not found"
End If

End Sub



--
Regards,

OssieMac


"Rick Rothstein" wrote:

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