Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Macro Help
I have a worksheet that contains follow up data that I would like my users,
via a userform to edit. I have constructed an "Event ID" that uniquely identifies each row in the spreadsheet. My hope was to have a search dropdown box that displays the current Event ID numbers, enable the user to verifiy that this is correct entry, then load the information on a userform for editing and completion. The Event ID is made by combining the Account Number with an Event Code then followed by the Excel Date Serial Number. The spreadsheet is built as follows: Event ID Acct Number Auditor 333333-NPSG9a-38439 333333 Jones 121212-NPSG2a1-38440 121212 Smith I set up the following code on a command button: Private Sub ExamineEventIDInfoButton_Click() sKey = GetFollowUpData.EventID.Value Set rnga = Range("EventID") Set c = rnga.Find(sKey, LookIn:=xlValues) If Not c Is Nothing Then MsgBox c.Row 'Row of Match 'Assign data in row to user controls GetFollowUpData.EventID = Cells(c.Row, 1) 'EventID GetFollowUpData.AcctNumberTxt = Cells(c.Row, 2) 'Acct Number GetFollowUpData.AuditorLast = Cells(c.Row, 3) 'Auditor Last Else MsgBox "No Match Found" End If End Sub I keep getting the "Row Found" as the first blank row on the spreadsheet. Should I place this on a different event? Is the search key the problem? Any advice? WilRn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Macro Help
Well,
Dummy me found my own answer. When in doubt double check all the reference values and field names. I used the wrong comboBox name on the secound line. My code should have been: Private Sub ExamineEventIDInfoButton_Click() sKey = GetFollowUpData.FINDEventID.Value Set rnga = Range("EventID") Set c = rnga.Find(sKey, LookIn:=xlValues) If Not c Is Nothing Then MsgBox c.Row 'Row of Match 'Assign data in row to user controls GetFollowUpData.EventID = Cells(c.Row, 1) 'EventID GetFollowUpData.AcctNumberTxt = Cells(c.Row, 2) 'Acct Number GetFollowUpData.AuditorLast = Cells(c.Row, 3) 'Auditor Last Else MsgBox "No Match Found" End If End Sub Boy, . . . do I feel sheeeeepppppiissshh! Baa Baa for now, WillRn "WillRn" wrote: I have a worksheet that contains follow up data that I would like my users, via a userform to edit. I have constructed an "Event ID" that uniquely identifies each row in the spreadsheet. My hope was to have a search dropdown box that displays the current Event ID numbers, enable the user to verifiy that this is correct entry, then load the information on a userform for editing and completion. The Event ID is made by combining the Account Number with an Event Code then followed by the Excel Date Serial Number. The spreadsheet is built as follows: Event ID Acct Number Auditor 333333-NPSG9a-38439 333333 Jones 121212-NPSG2a1-38440 121212 Smith I set up the following code on a command button: Private Sub ExamineEventIDInfoButton_Click() sKey = GetFollowUpData.EventID.Value Set rnga = Range("EventID") Set c = rnga.Find(sKey, LookIn:=xlValues) If Not c Is Nothing Then MsgBox c.Row 'Row of Match 'Assign data in row to user controls GetFollowUpData.EventID = Cells(c.Row, 1) 'EventID GetFollowUpData.AcctNumberTxt = Cells(c.Row, 2) 'Acct Number GetFollowUpData.AuditorLast = Cells(c.Row, 3) 'Auditor Last Else MsgBox "No Match Found" End If End Sub I keep getting the "Row Found" as the first blank row on the spreadsheet. Should I place this on a different event? Is the search key the problem? Any advice? WilRn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Macro | Excel Worksheet Functions | |||
macro search | Excel Discussion (Misc queries) | |||
Search In a Macro/VBA | Excel Discussion (Misc queries) | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming |