The Searching-For-Data-With-A-User-Form tool is exactly what I have been looking for. I have slightly modified it for my data, but it does not work when my "Record" is alphanumeric. I will work just fine if I use a number, but most of my record IDs are alphanumeric. On a side note, it would be nice to clear previous search results, especially when receiving the "Not Found" error message. Attached is my sample file.
Thank you,
David
I'm no good with Forms or VBA but have a formula based solution, provided you are on a modern Excel version. It uses data validation and the FILTER function. Using data validation is handy as it will prevent users from entering a non-existing code. If you prefer not to use data validation and have users enter codes freely, I've included a value of "Not found" for the [if-empty] argument in the FILTER function. It's all in the attached file.
If Forms and VBA is a must, perhaps @philipt can jump in and help you.
Hi David,
My original code was designed for numeric data (as you've discovered) so needs a tweak for alphanumeric.
In the cmdSearchBadge_Click sub change this
' Find the row in the table that the record is in
RecordRow = Application.Match(CLng(txtSearchBadge.Value), Range("Table1[BADGE]"), 0)
to this
' Find the row in the table that the record is in
If IsNumeric(txtSearchBadge.Value) Then
RecordRow = Application.Match(CLng(txtSearchBadge.Value), Range("Table1[BADGE]"), 0)
Else
RecordRow = Application.Match(txtSearchBadge.Value, Range("Table1[BADGE]"), 0)
End If
Regards
Phil