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

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