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]

According to your original code, in this field "txtUnitCoachSearch" you enter the data to search for, so what data did you enter when executing my code?

The code searches in columns O to Z, so you can put here a sample of your data where I can see that in some cell you have the same data that you entered in the "txtUnitCoachSearch" field. Use XL2BB too, look at my signature.

Dante Good Afternoon

Apologies but I have tried to copy the small amount of data I am using to text but XL2BB crashes Excel each time I try to capture the selection. I tried then to select the minimum option which resulted in masses of lines of unitelligible text being uploaded.
If it helps, the value in "txtUnitCoachSearch" will always refer to a unique value in the entire worksheet.

FYI I have 4 rows of information to test with, but eventually this figure will be around 6000.
In the "Passenger" worksheet, Columns A through N will always be populated on each row, but some or all of Rows O through AA may hold no value.

To test, I have entered the value 62625 into the "txtUnitCoachSearch" field on the UserForm. This value can be found in Cell Q3 of the "Passenger" Worksheet.

Also, the format of the cells are set to "General" and this works perfectly well in my other file.

I have also tried changing variable 1 to As String and As Integer, but with no success.

I do hope that you can help

David

 

[DanteAmor]

Replace this line
Set f = sh.Range("O:Z").Find(txtUnitCoachSearch, , xlValues, xlWhole)
For this
Set f = sh.Range("O:Z").Find(value(txtUnitCoachSearch.value), , xlValues, xlWhole)

 

[Denny57]

Replace this line
Set f = sh.Range("O:Z").Find(txtUnitCoachSearch, , xlValues, xlWhole)
For this
Set f = sh.Range("O:Z").Find(value(txtUnitCoachSearch.value), , xlValues, xlWhole)

Hi Dante
I now get a compile error *Sub or Function Not defined

 

[Denny57]

Hi Dante
I now get a compile error *Sub or Function Not defined

Error is based on first instance of additional "Value"

 

[DanteAmor]

Sorry, try this
Set f = sh.Range("O:Z").Find(val(txtUnitCoachSearch.value), , xlValues, xlWhole)

 

[Denny57]

Sorry, try this
Set f = sh.Range("O:Z").Find(val(txtUnitCoachSearch.value), , xlValues, xlWhole)

No error message and no message box to indicate no matching record , however, the userform is not being populated with the corresponding details so I cannot tell if the code is finding the match and just not uploading the information into the userform or is just not working.

 

[DanteAmor]

you have problems with your data, I have to review the data. you can share your file in dropbox or google drive.

 

[Denny57]

you have problems with your data, I have to review the data. you can share your file in dropbox or google drive.

Dante

The data is uploaded into the worksheet the same way for other files I have created and with the same cell formats. These have all worked fine but only require a single column search
I now have a separate search function installed on the file which uses ther same userform but searches a different numeric record but uses a different textbox and command button . This only has to search a single column ("A") and works perfectly and re=populates the userform as expected.

My problem has arisen when I need to find a unique record which could be in any one of 12 columns instead of just one. I have even tried by putting the 12 columns in a named group.

Unfortunately, I do not have access to be able to send through the file.

Thank you for all your help. I will let you know if I find a solution.

Best Regards

 

[DanteAmor]

Change xlwhole for xlpart

 

[dmt32]

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

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