Hi
I Have created a file with a userform through which I can Add, Search and Update records in a single worksheet by searching for a unique value in a single column. The code works well, so now I am working on a similar file, but the search needs to find a unique value which might be contained in one of up to 12 columns.
My user form has a dedicated textbox into which I enter the unique 5 or 6 digit number to be found and a dedicated Command Button to initiate the search.
I have reached the point where I need to code up the Command Button to initiate this search but the code I used for a single column is not appropriate in this instance
My aims are:-
1) Enter a 5 - 6 digit number into a dedicated textbox on the user form (txtSearch)
2) Initiate a search for that number in named range (of 12 columns and 6000 rows (CmdSearch)
3) If no Match - Return a message box "Match Not Found"
4) If Match - Re-populate the user form with all the details from the matched row with the corresponding records details
The following is an extract of paer of the code (the rest just re-populates the remaining 20 or so user form fields)
NOTE: Range "O:Z" are the 12 rowns but I have also enclosed these in a Named Range if that helps.
I welcome any assistance
I Have created a file with a userform through which I can Add, Search and Update records in a single worksheet by searching for a unique value in a single column. The code works well, so now I am working on a similar file, but the search needs to find a unique value which might be contained in one of up to 12 columns.
My user form has a dedicated textbox into which I enter the unique 5 or 6 digit number to be found and a dedicated Command Button to initiate the search.
I have reached the point where I need to code up the Command Button to initiate this search but the code I used for a single column is not appropriate in this instance
My aims are:-
1) Enter a 5 - 6 digit number into a dedicated textbox on the user form (txtSearch)
2) Initiate a search for that number in named range (of 12 columns and 6000 rows (CmdSearch)
3) If no Match - Return a message box "Match Not Found"
4) If Match - Re-populate the user form with all the details from the matched row with the corresponding records details
The following is an extract of paer of the code (the rest just re-populates the remaining 20 or so user form fields)
Code:
Private Sub cmdSearch_Click()
Dim sh As Worksheet, f As Range, i As Long
Set sh = Sheets("Sheet2")
Set f = sh.Range("O:Z").Find(Val(txtSearch.value), , xlValues, xlWhole)
If Not f Is Nothing Then
txtRecordNumber .value = sh.Cells(f.Row, 1).value
cboRecordType.value = sh.Cells(f.Row, 2).value
txtRecordClass.value = sh.Cells(f.Row, 3).valueNOTE: Range "O:Z" are the 12 rowns but I have also enclosed these in a Named Range if that helps.
I welcome any assistance
Last edited by a moderator: