Stop a find after the last occrance of the search value.

[Pats]

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

 

[Rick Rothstein]

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.

 

[OssieMac]

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

 

[Patrick C. Simonds]

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.

 

[Rick Rothstein]

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

 

[OssieMac]

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

 

[Rick Rothstein]

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 Rothstein]

A clarification... Because the OP set up the Find to search by rows, I
assumed he wanted to start looking from left to right across the row
starting just after the ActiveCell and then wrap to the next row and
continue searching the entire next row and so on until the bottom of the
data was reached.

 

[OssieMac]

Yes. You are correct Rick so thanks for that.

Just for the exercise I changed the code to include the reminder of the row
plus the remaining rows on the worksheet.

I realize that setting the range to search using Union can be done all in
one line of code but I always think it is easier to follow the code if it is
separated as I have done in the example.

Sub FindAfterActiveCell()
'Restrict the range being searched.

Dim C As Range
Dim rngToSearch1 As Range
Dim rngToSearch2 As Range
Dim rngToSearch As Range

Set rngToSearch1 = _
Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count))

Set rngToSearch2 = Range(Rows(ActiveCell.Row + 1), _
Rows(Rows.Count))

Set rngToSearch = Union(rngToSearch1, rngToSearch2)

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

 

[Rick Rothstein]

Yes, that approach would work as well. As for setting two ranges first and
then taking the Union of them... I understand that preference completely,
but my personal preference is to avoid creating variable that are not
needed. In production code, I would include a comment explaining what my
combined statement is doing.