Returning Data to a User Form

Denny57

Board Regular
Joined
Nov 23, 2015
Messages
235
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Haven't looked at your code that thoroughly but first try a space between Not and FoundCell within the last part of your code.
 
Upvote 0
Solution
You're welcome, glad it's sorted.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top