ForEach statement problem
 

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

ForEach statement problem
 

excelnut1954 wrote:
'******** 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/arch...e-find-method/

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

ForEach statement problem
 

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.