Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 175
Default Assigning a number to each record Found

I'm going to try this again. Obviously I didn't explain this very
well the other day. I get hung up on details sometimes, and maybe the
reader gets mislead as a result.

What I want is to somehow attach a unique number to a record(s) found
via a Find sub shown near the bottom (TestFind_POCurrent ). This would
be done so that I can show that number in a user form when the user is
looking at the record. I want to have this for when there is more than
1 record found (user will enter a PO number he wants to view, and there
will sometimes be more than one record on the list with that PO
number).

I have the UserForm13 that will show all of the fields of the record
found. This user form has TextBox15 that shows how many of that PO
number was found. I have a Next, and Previous button on this user form
that will show the next record, and the previous record, if there is
more than one on the list.

What I want is to have a new textbox on this user form that will show
which record the user is looking at. Each record found would have a
sequential number assigned to it.

Example: the user enters in a PO number to find that record(s). Suppose
that there are 4 such records on the list. UserForm13 will come up,
showing the 1st record. Textbox15 shows the number 4, indicating that
there are 4 records with this PO number. The user clicks the Next
button, and the next record will be shown. Textbox15 still shows the
number 4, since its only showing how many total records with that PO
number there are.
This is as far as I've gotten.

What I want is to have another textbox that will show the number 1 when
the 1st record is showing, the number 2 when the 2nd record is showing,
etc. And, those numbers will stay with each record while this user form
is up. So that when he clicks the Previous button, or the Next button,
over and over, back and forth, through it all, the 1st record will
always show number 1 in this new textbox, the 2nd record will show
number 2 in the new texbox, etc.

The end result is that I can show that the user is looking at Record 1
of 4, or Record 3 of 4, etc. Remember, I have the "4' part of this
example already, in TextBox15.

Here is the macro that shows the total records found during a search.
This number shows up in TextBox15 of UserForm13.
This code is in UserForm13
'Counter for how many of this PO number there are on the list
'This goes in TextBox15
CountPO = Application.CountIf(Range("J:J"), FindPOVal)
TextBox15.Value = CountPO

Here is the macro to perform the initial Find.

These are the declaration statements for the sub below:
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. Make sure you entered the
correct number." Unload UserForm12
UserForm12.Show
Else
strFirst = rngFound.Address
rngFound.Select

Unload UserForm12
UserForm13.Show
End If
End Sub

I also have subs to perform the FindNext, and FindPrevous commands,
which are executed via the Find and Previous buttons within UserForm13.

I'm not sure if the code I'm looking for would be put in
UserForm13, where I have the code that counts the total records found
(TextBox15 code shown above), or if it would be put in Sub
TestFind_POCurrent.

If anyone understands what I am looking for, and can help out, please
let me know.
I really appreciate all those who try to help.
Thanks,
J.O.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Assigning Color to a number kb489 Excel Discussion (Misc queries) 3 December 2nd 09 09:41 PM
I need to have a record number change for every record Puget Sound Courier Service Excel Discussion (Misc queries) 1 July 12th 09 03:32 PM
Assigning a number value to strings JRD Excel Worksheet Functions 1 June 26th 09 07:00 PM
Assigning a number to a name Adel1216 Excel Discussion (Misc queries) 1 March 30th 09 04:19 PM
Go to record found by Criteria in Data Form Paul Simon[_3_] Excel Programming 0 December 18th 03 03:31 PM


All times are GMT +1. The time now is 03:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"