Searching multiple columns then upl;oading into UserForm

[Denny57]

I have some code which works very well when a search is made through a user form and the search criteria is limited to one column (Column A) in a worksheet. This happily populates all details to the corresponding fields in the UserForm

Extract of the code
FIRST SEARCH COMMAND (Workng as required)
Private Sub cmdUnitSearch_Click()
Dim lastrow
Dim myfind As String

lastrow = Sheets("Passenger").Range("A" & Rows.Count).End(xlUp).Row
myfind = txtUnitNumber
For currentrow = 2 To lastrow
If Cells(currentrow, 1).Text = myfind Then
txtUnitNumber.Value = Cells(currentrow, 1).Value
cboUnitStockType.Value = Cells(currentrow, 2).Value
txtUnitClass.Value = Cells(currentrow, 3).Value
THERE ARE ANOTHER 25 PLUS RECORDS

I now wish to search for a unique record which may be stored in a wider range of columns (12 to be precise)

The variable "myfind" searches for a match to a value in textbox "txtUnitCoachSearch"

I have thought about using IF with ElseIF but this means that I need to repeat a large number of lines of code for each ElseIF.

Also, I beleive that I may have the Range in the lastrow variable incorrect as a Runtime Error 1004 appears. I have checked the field names and other input have been typed correctly so I assume that this must be an issue with the Range.

(currentrow, 15) refers to Column "o" in the worksheet

SECOND SEARCH COMAND
Private Sub cmdCoachNumberSearch_Click()

Dim lastrow
Dim myfind As String

lastrow = Sheets("Passenger").Range("O:Z" & Rows.Count).End(xlUp).Row
myfind = txtUnitCoachSearch
For currentrow = 2 To lastrow
If Cells(currentrow, 15).Text = myfind Then
txtUnitNumber.Value = Cells(currentrow, 1).Value
cboUnitStockType.Value = Cells(currentrow, 2).Value
txtUnitClass.Value = Cells(currentrow, 3).Value
THERE ARE ANOTHER 25 PLUS RECORDS

I would appreciate any help

 

[Denny57]

Change xlwhole for xlpart

Good Morning Dante

Thank you The code works as far as populating the userform. However, the reason for the search was so that records could be updated.

My single row search uses "Currentrow" to identify the row containing the search record and then mt

Untested & based on code you published in #post 5 see if this update will do what you want

Dim currentrow As Long
Private Sub cmdCoachNumberSearch_Click()
    Dim myFind As Variant
    Dim FoundCell As Range
    Dim ws As Worksheet
  
    Set ws = ThisWorkbook.Worksheets("Passenger")
    myFind = txtUnitCoachSearch
    If Len(myFind) = 0 Then Exit Sub
  
    Set FoundCell = ws.Range("A1").CurrentRegion.Find(myFind, LookIn:=xlValues, lookat:=xlWhole)
    If Not FoundCell Is Nothing Then
        currentrow = FoundCell.Row
        txtUnitNumber.Value = ws.Cells(currentrow, 1).Value
        cboUnitStockType.Value = ws.Cells(currentrow, 2).Value
        txtUnitClass.Value = ws.Cells(currentrow, 3).Value
        cboUnitStatus.Value = ws.Cells(currentrow, 4).Value
        DTPicker2.Value = ws.Cells(currentrow, 5).Value
        txtUnitLiveryCode.Value = ws.Cells(currentrow, 6).Value
        txtUnitLiveryDescription.Value = ws.Cells(currentrow, 7).Value
        txtUnitOwnerCode.Value = ws.Cells(currentrow, 8).Value
        txtUnitOwnerName.Value = ws.Cells(currentrow, 9).Value
        txtUnitOperatorCode.Value = ws.Cells(currentrow, 10).Value
        txtUnitOperatorName.Value = ws.Cells(currentrow, 11).Value
        txtUnitDepotCode.Value = ws.Cells(currentrow, 12).Value
        txtUnitDepotLocation.Value = ws.Cells(currentrow, 13).Value
        txtUnitDepotOperator.Value = ws.Cells(currentrow, 14).Value
        txtUnitCoach1.Value = ws.Cells(currentrow, 15).Value
        txtUnitCoach2.Value = ws.Cells(currentrow, 16).Value
        txtUnitCoach3.Value = ws.Cells(currentrow, 17).Value
        txtUnitCoach4.Value = ws.Cells(currentrow, 18).Value
        txtUnitCoach5.Value = ws.Cells(currentrow, 19).Value
        txtUnitCoach6.Value = ws.Cells(currentrow, 20).Value
        txtUnitCoach7.Value = ws.Cells(currentrow, 21).Value
        txtUnitCoach8.Value = ws.Cells(currentrow, 22).Value
        txtUnitCoach9.Value = ws.Cells(currentrow, 23).Value
        txtUnitCoach10.Value = ws.Cells(currentrow, 24).Value
        txtUnitCoach11.Value = ws.Cells(currentrow, 25).Value
        txtUnitCoach12.Value = ws.Cells(currentrow, 26).Value
        txtUnitName.Value = ws.Cells(currentrow, 27).Value

    Else
        MsgBox myFind & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
    txtUnitCoachSearch.SetFocus
End Sub

Update should allow you to search across all ranges for myFind value.

Note your variable CurrentRow is outside of the procedure – this should be placed at the TOP of your forms coded page if you intend to return the updated record back to your worksheet.

Hope Helpful

Dave

Dave

Many thanks for this. it works perfectly and also meets the coding I have for updating records.

 

[Denny57]

Change xlwhole for xlpart

Dante

Thank you for your help. Your code did return the records to the useform but then I had issues with the upload of amended records.

All is now well so once again thank you.

David

 

[dmt32]

Many thanks for feedback - glad we were able to assist in resolving for you

Dave