I am currently populating an excel worksheet with UK rolling stock records using a User Form which allows not only the input of new records but has the ability to search for a record and populate the user form with all the corresponding information and allow any of these to be amended. The form has two search criteria,the first by a unique stock number the details of which are only found in column F. This search works perfectly.
CODE
Private Sub cmdStockSearch_Click()
'Used to search for a unique stock number in the database and return all corresponding values to the user form
Dim Res As Variant
Dim lastrow
Dim myFind As String
Res = Application.Match(txtStockNumberSearch, Sheets("Stock Records").Range("F2:F7500"), 0)
If IsError(Res) Then
MsgBox "Stock Number Not Found", vbInformation, "Stock Number Not Found"
Call UserForm_Initialize
cboType.SetFocus
Exit Sub
End If
lastrow = Sheets("Stock Records").Range("F" & Rows.Count).End(xlUp).Row
myFind = txtStockNumberSearch
For currentrow = 2 To lastrow
If Cells(currentrow, 6).Text = myFind Then
cboType.Value = Cells(currentrow, 1).Value....... (Subsequent code selects the remaining details to be used to populate the user form)
The second search uses a unique coach number, the details of which might be stored in Columns W through AH. (Note that not all stock records have coaches or the same number of coaches). As the search record might be in any one of 12 columns, I cannot use the same code as for the search by stock number.
Coach Search Code
Private Sub cmdCoachSearch_Click()
'Used to search for a unique coach number in the database and return all corresponding values to the user form
Dim myFind As Variant
Dim FoundCell As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Stock Records")
myFind = txtCoachNumberSearch
If Len(myFind) = 0 Then Exit Sub
Set FoundCell = ws.Range("A1").CurrentRegion.Find(myFind, LookIn:=xlValues, lookat:=xlWhole)
If NotFoundCell Is Nothing Then
currentrow = FoundCell.Row
cboType.Value = Cells(currentrow, 1).Value..... (Subsequent code selects the remaining details to be used to populate the user form)
NotFoundCell returns a runtime error "Object Not Found" but if I remove the Not then a search returns a message box advising that the search record cannot be located in the database although a corresponding record does exist.
I should be grateful if someone can advise me of the correct code to search the range W2:AH7500 for the unique match for information in myFind = txtCoachNumberSearch and identify the corresponding record to populate the user form with all the item details.
Many thanks in Advance
CODE
Private Sub cmdStockSearch_Click()
'Used to search for a unique stock number in the database and return all corresponding values to the user form
Dim Res As Variant
Dim lastrow
Dim myFind As String
Res = Application.Match(txtStockNumberSearch, Sheets("Stock Records").Range("F2:F7500"), 0)
If IsError(Res) Then
MsgBox "Stock Number Not Found", vbInformation, "Stock Number Not Found"
Call UserForm_Initialize
cboType.SetFocus
Exit Sub
End If
lastrow = Sheets("Stock Records").Range("F" & Rows.Count).End(xlUp).Row
myFind = txtStockNumberSearch
For currentrow = 2 To lastrow
If Cells(currentrow, 6).Text = myFind Then
cboType.Value = Cells(currentrow, 1).Value....... (Subsequent code selects the remaining details to be used to populate the user form)
The second search uses a unique coach number, the details of which might be stored in Columns W through AH. (Note that not all stock records have coaches or the same number of coaches). As the search record might be in any one of 12 columns, I cannot use the same code as for the search by stock number.
Coach Search Code
Private Sub cmdCoachSearch_Click()
'Used to search for a unique coach number in the database and return all corresponding values to the user form
Dim myFind As Variant
Dim FoundCell As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Stock Records")
myFind = txtCoachNumberSearch
If Len(myFind) = 0 Then Exit Sub
Set FoundCell = ws.Range("A1").CurrentRegion.Find(myFind, LookIn:=xlValues, lookat:=xlWhole)
If NotFoundCell Is Nothing Then
currentrow = FoundCell.Row
cboType.Value = Cells(currentrow, 1).Value..... (Subsequent code selects the remaining details to be used to populate the user form)
NotFoundCell returns a runtime error "Object Not Found" but if I remove the Not then a search returns a message box advising that the search record cannot be located in the database although a corresponding record does exist.
I should be grateful if someone can advise me of the correct code to search the range W2:AH7500 for the unique match for information in myFind = txtCoachNumberSearch and identify the corresponding record to populate the user form with all the item details.
Many thanks in Advance