Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning Color to a number | Excel Discussion (Misc queries) | |||
I need to have a record number change for every record | Excel Discussion (Misc queries) | |||
Assigning a number value to strings | Excel Worksheet Functions | |||
Assigning a number to a name | Excel Discussion (Misc queries) | |||
Go to record found by Criteria in Data Form | Excel Programming |