Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counter for records found during Find
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counter for records found during Find
You may want to look at the FoundFiles property and object.
Ed "excelnut1954" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counter for records found during Find
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counter for records found during Find
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 "excelnut1954" wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get number of filtered records found displayed in a cell? | Excel Worksheet Functions | |||
In Excel, How do I get AutoFilter to provide# of records found? | Excel Discussion (Misc queries) | |||
How to change left bottom note that tell number records found? | New Users to Excel | |||
how do I display filtered records found | Excel Discussion (Misc queries) | |||
Advanced Filter: Number of Records Found | Excel Discussion (Misc queries) |