ForEach statement problem

[excelnut1954]

This macro will search for records based on a PO# given by the user.
Often, there are more than one record with that PO#. The code, up to
the End If statement works. I wanted to add code so that I can show in
UserForm13 which of the found records is showing (1 of 4, 2 of 4, etc).
I already have a textbox showing the total records found. I wanted the
ForEach part to "assign" a distinct number to that record, so I can
show it in another textbox.
Obviously, I'm not setting this up correctly. In the ForEach
statement, I'm getting an error when it reads rndFound.
Can someone help me with ideas on what I need to change here?
Thanks
Much appreciated
J.O.

'These are the Standard module declarations
Public rngToSearch As Range
Public rngFound As Range
Public strFirst As String
Public FindPOVal As String

Sub TestFind_POCurrent()
Worksheets("Official List").Activate

Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "This record was not found. Please try again."
Unload UserForm12
UserForm12.Show
Else

strFirst = rngFound.Address
rngFound.Selec
End If

Dim RecordsFound As Integer

'******** this is the part showing the error, in the rngFound part.
*******
'******** I thought I could use rngFound to identify each record in the
range. ******
For Each rngFound In rngToSearch .
RecordsFound = RecordsFound + 1

Next rngFound

Unload UserForm12
UserForm13.Show

End Sub

 

[Dick Kusleika]

'******** this is the part showing the error, in the rngFound part.
*******
'******** I thought I could use rngFound to identify each record in
the range. ******
For Each rngFound In rngToSearch .
RecordsFound = RecordsFound + 1

Next rngFound

This part isn't doing what you think. rngFound pointed to the cell returned
by the Find method, but using it in the For Each destory's that pointer and
points it to J1 (on the first loop).

Since you don't use the After argument in the Find method, Find is always
going to return the first one it finds. If you display "Record x of y", x
will always be 1. To find y, you need to use FindNext until it loops back
to the first one and count them along the way. All this should be in the
Else part of your If rngFound Is Nothing block.

If you want the user to be able to loop through all the POs, you'll need
another variable to hold all the cells that are found.

Else
strFirst = rngFound.Address
Set rngAllFound = rngFound
Do
Set rngFound = rngToSearch.FindNext(rngFound)
Set rngAllFound = Union(rngAllFound, rngFound)
Loop Until rngFound.Address = strFirst

RecordsFound = rngAllFound.Cells.Count

Now rngAllFound will be a range of cells with that PO number and rngFound
will be pointing to the first one it finds.

If you can clarify what you're doing, I may be able to give you more
specific help. You can see an example of the Find method here

http://www.dailydoseofexcel.com/archives/2004/03/29/the-find-method/

 

[excelnut1954]

Thanks for responding.....

These are the other 2 subs that handle the Next and Previous buttons in
UserForm13. When I have things set up (prior to trying the ForEach) ,
everything worked ok. The user could hit Next over and over, same with
the Previous button, and it would work fine.
What I'm trying to do is to be able to show a number in TextBox16
that will show which record the user is looking at. I already have
TextBox15 which shows the total records found in the initial Find. That
code is under the 2 subs. I wanted to assign a number to each record
found. That number would show up in TextBox16. 1st record = 1, 2nd
record = 2, etc.

Whatever help you can offer, I would appreciate.
Thanks,
J.O.

Sub TestFindNext_POCurrent()
Worksheets("Official List").Activate
Set rngFound = rngToSearch.FindNext(rngFound)
rngFound.Select
If rngFound.Address = strFirst Then
MsgBox "There are no other records with this PO/PL."

Else

Unload UserForm13
UserForm13.Show
End If
End Sub
********************************
Sub TestFindPrevious_POCurrent()
Worksheets("Official List").Activate
Set rngFound = rngToSearch.FindPrevious(rngFound)
rngFound.Select

Unload UserForm13
UserForm13.Show

End Sub


'Counter for how many of this PO/PL there are on the list
'This goes in TextBox15
CountPO = Application.CountIf(Range("J:J"), FindPOVal)
TextBox15.Value = CountPO

 

[Dick Kusleika]

JO

I think you will need to loop through the whole range and keep a count of
the cells you find. Then when you get to the one whose Address property =
rngFound.Address, you would stop counting and that would be the relative
position of rngFound in the body of all cells that match your criteria. It
seems like a lot of work, but I can't figure out how else you would know
which it was in the list.

You probably don't want to do a whole lot of rewriting, but I'll show you
how I would do it. I would create a range of all the found cells and pass
that into the userform. Then, I wouldn't close and reopen the userform,
just use the FindNext and FindPrevious methods when the users clicks the
buttons. Here's an example of what I mean

http://www.dailydoseofexcel.com/archives/2006/03/31/viewing-a-limited-range-on-a-userform/

 

[excelnut1954]

Thanks for getting back. I'll check this out this weekend. I just find
it hard to believe that its impossible to just be able to assign a
number, or a letter for that matter, to a record. You know... the 1st
record found shall be named "X". and, X will = 1. The 2nd record found
will be named "Y", and Y will = 2. So that when record X is shown, the
textbox will show a 1. When record Y is shown, that textbox will show a
2. That textbox would be next to the word "OF", and next to that will
be the textbox that I already have that shows how many total records
were found. 1 OF 2 2 OF 2

I'll keep trying to learn more about memory, and maybe I'll stumble on
it.
I really appreciate your effort.
Thanks,
J.O.