Counter for records found during Find

[excelnut1954]

Here's what I have:
The user wants to find a record. He clicks a button to request this,
and enters a PO number. A sub in a standard module is executed that
performs a Find. Then another UserForm comes up showing the record. One
of the textboxes of this UserForm will show the total number of records
found under this PO number. Here's the coding for this:

This code is in UserForm13
'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

FindPOVal is a declaration in the standard module, and is a variable in
the Find command.
Public FindPOVal As String

There are Next and Previous buttons on this UserForm, that will allow
the user to view each of the found records, backward, and forward.

Here's what I need:
What I want to add to this UserForm is a textbox that will show which
record of those found that is being viewed at that time.
I already have the total count. I need to attach a number for each
found record.

Example: 4 records are found. The 1st records comes up, and the number
in TextBox15 is 4. The number in this new textbox I want to add will
be 1.
User clicks the Next button. Record 2 will appear, and the new text box
will show the number 2. User clicks Next again, and record 3 appears.
TextBox15 shows 3.
The user clicks the Previous button, and record 2 comes up again.
Textbox15 shows 2.

Any suggestions on how to add this counter for the new textbox?
Also, where would I put the code? In UserForm13? Or in the standard
module where the Find command is?

Thanks,
J.O.

 

[Ed]

You may want to look at the FoundFiles property and object.

Ed

 

[excelnut1954]

I looked at the FoundFile material in Help, and I'm not certain if I
didn't communicate what I'm looking for very well, or if you just
misunderstood. I'm refering to records within a worksheet.

After my macro performs a Find, the coding shown in my original post
will count how many were found, and when the UserForm comes up,
textbox15 will show that number. I want to attach a sequential number
to each of the found records, so that when the user clicks the Next or
Previous button on the form, I can show which record it is. Example 1
of 4, 2 of 4, etc.

If FoundFile WOULD apply to this, let me know, and I'll have to find
other resources for the command.
Thanks for your help.
J.O.

 

[Ed]

You are right - I goofed! FoundFiles only works with, well, files.

I have a macro that searches through a rnage on a sheet to find a text
string. When found, it writes the row number into an array. By iterating
through the array, I can get the row number of a certain item. Here is what
I have; I hope you can find something of use in it.

Ed

Sub SelectiveRowFind()

Dim myTarget As String
Dim myFind As Range
Dim rngSheet As Range
Dim rngLook As Range
Dim i As Integer, j As Integer
Dim x As Integer, y As Integer
Dim Cell As Object
Dim myFound() As Variant

Sheets("Sheet1").Activate
i = 0
j = 0

' Get text string to search for
myTarget = ""
myTarget = Application.InputBox("What text are you searching for?")
If myTarget = "" Or myTarget = "False" Then GoTo Bye

' Set range to used range on worksheet
If ActiveSheet.AutoFilterMode Then
Set rngSheet = ActiveSheet.AutoFilter.Range
Else
Set rngSheet = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
End If

' Resize range to exclude header row
Set rngSheet = rngSheet.Offset(1, 0).Resize(rngSheet.Rows.Count - 1)

' Do search
y = rngSheet.Rows.Count
For x = 1 To y
Set rngLook = rngSheet.Rows(x)
For Each Cell In rngLook
i = Cell.Row
If Not Rows(i).Hidden Then
Rows(i).Select
Set myFind = Rows(i).Find(What:=myTarget, LookAt:=xlPart)
If Not myFind Is Nothing Then
j = j + 1
ReDim Preserve myFound(j)
myFound(j) = i
GoTo NextRow
End If
End If
Next Cell
NextRow:
Next x

MsgBox "There were " & j & " matches found."

For x = 1 To j
MsgBox myFound(x)
Next x

Bye:
End Sub