Isn't there always one more question :-)

Try this one:

Sub NextRow()
'
' Macro4 Macro
'
'This routine finds each occurance of NoXXX in column AA so that
' multipage 2 of NoShowDataInput UserForm can display prior No Shows


Dim c As Range
Dim rngArr() As Range
Dim rng As Range
ReDim rngArr(0)
Dim Counter As Long

On Error GoTo Done
ActiveSheet.Columns("AA").Hidden = False
Set c = Cells.Find(What:="NoXXX", After:=Range("AA6"),
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)


If Not c Is Nothing Then
Do
Set rngArr(UBound(rngArr)) = c
ReDim Preserve rngArr(UBound(rngArr) + 1)
Set c = Cells.Find(What:="NoXXX", After:=Range(c.Address),
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
Counter = Counter + 1
Loop Until c.Address = rngArr(0).Address Or Range("AA1") - 1 =
Counter
End If
ReDim Preserve rngArr(UBound(rngArr) - 1)
Set rng = rngArr(UBound(rngArr))
Debug.Print rng.Address
Done:
ActiveSheet.Columns("AA").Hidden = True
End Sub

Regards,
Per

On 24 Sep., 19:40, "Patrick C. Simonds" wrote:
One more question (I know we always say that)

Can I limit the number of times this loops through looking for occurrences
of NoXXX? *I would place the count of NoXXX in cell AA1 *(minus 1, I need to
exclude the last occurrence of NoXXX, *I modified your code to start its
looking in cell AA7).



Hi

I would store the the found cells in an array, like this:

Sub NextRow()
'
' Macro4 Macro
'
'This routine finds each occurance of NoXXX in column AA so that
multipage 2 of NoShowDataInput UserForm can display prior No Shows

*Dim c As Range
*Dim rngArr() As Range
*Dim rng As Range
*ReDim rngArr(0)
*On Error GoTo Done
*ActiveSheet.Columns("AA").Hidden = False
*Set c = Cells.Find(What:="NoXXX", After:=Range("AA7"),
LookIn:=xlValues, _
* * * * * * * * * * LookAt:=xlPart, SearchOrder:=xlByRows, _
* * * * * * * * * * SearchDirection:=xlNext, MatchCase:=False, _
* * * * * * * * * * SearchFormat:=False)

*If Not c Is Nothing Then
* *Do
* * * *Set rngArr(UBound(rngArr)) = c
* * * *ReDim Preserve rngArr(UBound(rngArr) + 1)
* * * *Set c = Cells.Find(What:="NoXXX", After:=Range(c.Address),
LookIn:=xlValues, _
* * * * * * * * * * LookAt:=xlPart, SearchOrder:=xlByRows, _
* * * * * * * * * * SearchDirection:=xlNext, MatchCase:=False, _
* * * * * * * * * * SearchFormat:=False)
* *Loop Until c.Address = rngArr(0).Address
*End If
*ReDim Preserve rngArr(UBound(rngArr) - 1)
*Set rng = rngArr(UBound(rngArr))
*Debug.Print rng.Address
Done:
* *ActiveSheet.Columns("AA").Hidden = True
End Sub

Regards,
Per- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -