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

 

[Yongle]

search for match in column A and IF match is FOUND then this happens in the current code ...
txtUnitNumber.Value = Cells(FoundCellRow, "A").Value
cboUnitStockType.Value = Cells(FoundCellRow, "B").Value
txtUnitClass.Value = Cells(FoundCellRow, "C").Value

Q.
IF match is NOT FOUND which columns are searched next is it O, P, Q ... to Z ?
Q.
Are values returned the same 3 cells in that row ?
txtUnitNumber.Value = Cells(FoundCellRow, "A").Value
cboUnitStockType.Value = Cells(FoundCellRow, "B").Value
txtUnitClass.Value = Cells(FoundCellRow, "C").Value
Q.
Are you finding a match for text or a numeric value ?

 

[Denny57]

search for match in column A and IF match is FOUND then this happens in the current code ...
txtUnitNumber.Value = Cells(FoundCellRow, "A").Value
cboUnitStockType.Value = Cells(FoundCellRow, "B").Value
txtUnitClass.Value = Cells(FoundCellRow, "C").Value

Q.
IF match is NOT FOUND which columns are searched next is it O, P, Q ... to Z ?
Q.
Are values returned the same 3 cells in that row ?
txtUnitNumber.Value = Cells(FoundCellRow, "A").Value
cboUnitStockType.Value = Cells(FoundCellRow, "B").Value
txtUnitClass.Value = Cells(FoundCellRow, "C").Value
Q.
Are you finding a match for text or a numeric value ?

Hi Yongle

Just to clarify.. The search on A is for a different user form and file and only requires a search on one column. If no match is found, then a Message Box appears and no further action is required.

I have a similar file which contains much more information and now I have a need to widen the search parameters. The records to be searched are stored in Columns O through Z and whilst the search information is unique, It can be in any of the cells in this range.
Once located, I am looking for all the details in that row to be uploaded back into the UserForm.
In both cases the details look numeric but out of necessity are being stored as text.

Hope this helps

 

[Yongle]

Try this

VBA below searches columns O to Z in sheet "Passenger" for string myFind ( below assigned "the dog")
Amend sheet name to the correct one and assign myFind the correct variable or textbox value etc
It looks for he FIRST found value only
Row r is the row containing the found text

Sub FindMatch()
    Dim c As Long, r As Long, myFind As String, ws As Worksheet
    Set ws = Sheets("Passenger")
    myFind = "the dog"

    For c = 15 To 26
        On Error Resume Next
        r = WorksheetFunction.Match(myFind, ws.Columns(c), 0)
        On Error GoTo 0
        If r > 1 Then
            MsgBox ws.Cells(r, "A").Value
'this is where you write the details in row "r" into the UserForm.
            Exit For
        Else
            If r = 26 Then MsgBox "not found", vbExclamation, ""
        End If
    Next c
End Sub

 

[Denny57]

Hi Yongle

Here is the complete code as you provided.

Unfortunately, I get neither positive or negative responses nor any runtime errors, nothing happens when I click on the command button

Here is the entire code

Private Sub cmdCoachNumberSearch_Click()

'Dim lastrow
'lastrow = Sheets("Passenger").Range("O2:Z5000" & Rows.Count).End(xlUp).Row
'For currentrow = 2 To lastrow
'If Cells(currentrow, 17).Text = myfind Then

Dim c As Long
Dim r As Long
Dim myFind As String
Dim ws As Worksheet
Set ws = Sheets("Passenger")
myFind = txtUnitCoachSearch

For c = 15 To 26
On Error Resume Next
r = WorksheetFunction.Match(myFind, ws.Columns(c), 0)
On Error GoTo 0
If r > 1 Then
MsgBox ws.Cells(r, "A").Value

txtUnitNumber.Value = Cells(currentrow, 1).Value
cboUnitStockType.Value = Cells(currentrow, 2).Value
txtUnitClass.Value = Cells(currentrow, 3).Value
cboUnitStatus.Value = Cells(currentrow, 4).Value
DTPicker2.Value = Cells(currentrow, 5).Value
txtUnitLiveryCode.Value = Cells(currentrow, 6).Value
txtUnitLiveryDescription.Value = Cells(currentrow, 7).Value
txtUnitOwnerCode.Value = Cells(currentrow, 8).Value
txtUnitOwnerName.Value = Cells(currentrow, 9).Value
txtUnitOperatorCode.Value = Cells(currentrow, 10).Value
txtUnitOperatorName.Value = Cells(currentrow, 11).Value
txtUnitDepotCode.Value = Cells(currentrow, 12).Value
txtUnitDepotLocation.Value = Cells(currentrow, 13).Value
txtUnitDepotOperator.Value = Cells(currentrow, 14).Value
txtUnitCoach1.Value = Cells(currentrow, 15).Value
txtUnitCoach2.Value = Cells(currentrow, 16).Value
txtUnitCoach3.Value = Cells(currentrow, 17).Value
txtUnitCoach4.Value = Cells(currentrow, 18).Value
txtUnitCoach5.Value = Cells(currentrow, 19).Value
txtUnitCoach6.Value = Cells(currentrow, 20).Value
txtUnitCoach7.Value = Cells(currentrow, 21).Value
txtUnitCoach8.Value = Cells(currentrow, 22).Value
txtUnitCoach9.Value = Cells(currentrow, 23).Value
txtUnitCoach10.Value = Cells(currentrow, 24).Value
txtUnitCoach11.Value = Cells(currentrow, 25).Value
txtUnitCoach12.Value = Cells(currentrow, 26).Value
txtUnitName.Value = Cells(currentrow, 27).Value
Exit For
Else
If r = 26 Then MsgBox "Not Found", vbExclamation, ""
End If
Next c
txtUnitCoachSearch.SetFocus



End Sub

I appreciate that your change applies r to the row containing the records to be uploaded into the userform but I have tried changing "currentrow" to "r".

Nothing happens

Regards

 

[Yongle]

The code I provided does work if it finds a match
Did you try running the code simply changing the obvious values to see if the match is found?

If the expected match is not found then VBA is trying to match things that do not match. Perhaps converting the string to a number at point of checking the match is required
r = WorksheetFunction.Match(CDbl(myFind), ws.Columns(c), 0)

"In both cases the details look numeric but out of necessity are being stored as text."
I do not understand the above

The textbox contains a string
But are the numbers in the cells treated as numeric or string?

If you do not know use this formula
=T(A1)
If ithe formula returns the same value as A1, then Excel is treating that value as text
If the formula returns nothing, then the number in A1 is treated as a numeric

 

[DanteAmor]

Try the following to search with the Find method.

Private Sub cmdCoachNumberSearch_Click()
  Dim sh As Worksheet, f As Range, i As Long
  
  Set sh = Sheets("Passenger")
  Set f = sh.Range("O:Z").Find(txtUnitCoachSearch, , xlValues, xlWhole)
  If Not f Is Nothing Then
    txtUnitNumber.Value = sh.Cells(f.Row, 1).Value
    cboUnitStockType.Value = sh.Cells(f.Row, 2).Value
    txtUnitClass.Value = sh.Cells(f.Row, 3).Value
    cboUnitStatus.Value = sh.Cells(f.Row, 4).Value
    DTPicker2.Value = sh.Cells(f.Row, 5).Value
    txtUnitLiveryCode.Value = sh.Cells(f.Row, 6).Value
    txtUnitLiveryDescription.Value = sh.Cells(f.Row, 7).Value
    txtUnitOwnerCode.Value = sh.Cells(f.Row, 8).Value
    txtUnitOwnerName.Value = sh.Cells(f.Row, 9).Value
    txtUnitOperatorCode.Value = sh.Cells(f.Row, 10).Value
    txtUnitOperatorName.Value = sh.Cells(f.Row, 11).Value
    txtUnitDepotCode.Value = sh.Cells(f.Row, 12).Value
    txtUnitDepotLocation.Value = sh.Cells(f.Row, 13).Value
    txtUnitDepotOperator.Value = sh.Cells(f.Row, 14).Value
    For i = 1 To 12
      Controls("txtUnitCoach" & i).Value = sh.Cells(f.Row, i + 14).Value
    Next
  Else
    MsgBox "Not Found", vbExclamation
  End If
  txtUnitCoachSearch.SetFocus
End Sub

 

[Denny57]

Try the following to search with the Find method.

Private Sub cmdCoachNumberSearch_Click()
  Dim sh As Worksheet, f As Range, i As Long
 
  Set sh = Sheets("Passenger")
  Set f = sh.Range("O:Z").Find(txtUnitCoachSearch, , xlValues, xlWhole)
  If Not f Is Nothing Then
    txtUnitNumber.Value = sh.Cells(f.Row, 1).Value
    cboUnitStockType.Value = sh.Cells(f.Row, 2).Value
    txtUnitClass.Value = sh.Cells(f.Row, 3).Value
    cboUnitStatus.Value = sh.Cells(f.Row, 4).Value
    DTPicker2.Value = sh.Cells(f.Row, 5).Value
    txtUnitLiveryCode.Value = sh.Cells(f.Row, 6).Value
    txtUnitLiveryDescription.Value = sh.Cells(f.Row, 7).Value
    txtUnitOwnerCode.Value = sh.Cells(f.Row, 8).Value
    txtUnitOwnerName.Value = sh.Cells(f.Row, 9).Value
    txtUnitOperatorCode.Value = sh.Cells(f.Row, 10).Value
    txtUnitOperatorName.Value = sh.Cells(f.Row, 11).Value
    txtUnitDepotCode.Value = sh.Cells(f.Row, 12).Value
    txtUnitDepotLocation.Value = sh.Cells(f.Row, 13).Value
    txtUnitDepotOperator.Value = sh.Cells(f.Row, 14).Value
    For i = 1 To 12
      Controls("txtUnitCoach" & i).Value = sh.Cells(f.Row, i + 14).Value
    Next
  Else
    MsgBox "Not Found", vbExclamation
  End If
  txtUnitCoachSearch.SetFocus
End Sub

Good morning Dante

I have entered your code and regretably I get no response, either positive or negative ( e.g. Runtime error). To check the command button is working I changed the last instruction (Highlighted) and this part works fine.

Private Sub cmdCoachNumberSearch_Click()

Dim sh As Worksheet, f As Range, i As Long

Set sh = Sheets("Passenger")
Set f = sh.Range("O:Z").Find(txtUnitCoachSearch, , xlValues, xlWhole)
If Not f Is Nothing Then
txtUnitNumber.Value = sh.Cells(f.Row, 1).Value
cboUnitStockType.Value = sh.Cells(f.Row, 2).Value
txtUnitClass.Value = sh.Cells(f.Row, 3).Value
cboUnitStatus.Value = sh.Cells(f.Row, 4).Value
DTPicker2.Value = sh.Cells(f.Row, 5).Value
txtUnitLiveryCode.Value = sh.Cells(f.Row, 6).Value
txtUnitLiveryDescription.Value = sh.Cells(f.Row, 7).Value
txtUnitOwnerCode.Value = sh.Cells(f.Row, 8).Value
txtUnitOwnerName.Value = sh.Cells(f.Row, 9).Value
txtUnitOperatorCode.Value = sh.Cells(f.Row, 10).Value
txtUnitOperatorName.Value = sh.Cells(f.Row, 11).Value
txtUnitDepotCode.Value = sh.Cells(f.Row, 12).Value
txtUnitDepotLocation.Value = sh.Cells(f.Row, 13).Value
txtUnitDepotOperator.Value = sh.Cells(f.Row, 14).Value
For i = 1 To 12
Controls("txtUnitCoach" & i).Value = sh.Cells(f.Row, i + 14).Value
Next
Else
MsgBox "Not Found", vbExclamation
End If
Call UserForm_Initialize (This part works)
End Sub

Finally
I call the details populated into the corresponding fields in the Userform so I can make required changes to records and then my "Update" code uses "currentrow" to determine in the "Update" command code (Extract of Update Code)
Cells(currentrow, 1).Value = txtUnitNumber.Value
Cells(currentrow, 2).Value = cboUnitStockType.Value

What changes might I need to make to the update code to allow the search code you provided to update the original record, once the search code works.

Many thanks

 

[Denny57]

Good morning Dante

I have entered your code and regretably I get no response, either positive or negative ( e.g. Runtime error). To check the command button is working I changed the last instruction (Highlighted) and this part works fine.

Private Sub cmdCoachNumberSearch_Click()

Dim sh As Worksheet, f As Range, i As Long

Set sh = Sheets("Passenger")
Set f = sh.Range("O:Z").Find(txtUnitCoachSearch, , xlValues, xlWhole)
If Not f Is Nothing Then
txtUnitNumber.Value = sh.Cells(f.Row, 1).Value
cboUnitStockType.Value = sh.Cells(f.Row, 2).Value
txtUnitClass.Value = sh.Cells(f.Row, 3).Value
cboUnitStatus.Value = sh.Cells(f.Row, 4).Value
DTPicker2.Value = sh.Cells(f.Row, 5).Value
txtUnitLiveryCode.Value = sh.Cells(f.Row, 6).Value
txtUnitLiveryDescription.Value = sh.Cells(f.Row, 7).Value
txtUnitOwnerCode.Value = sh.Cells(f.Row, 8).Value
txtUnitOwnerName.Value = sh.Cells(f.Row, 9).Value
txtUnitOperatorCode.Value = sh.Cells(f.Row, 10).Value
txtUnitOperatorName.Value = sh.Cells(f.Row, 11).Value
txtUnitDepotCode.Value = sh.Cells(f.Row, 12).Value
txtUnitDepotLocation.Value = sh.Cells(f.Row, 13).Value
txtUnitDepotOperator.Value = sh.Cells(f.Row, 14).Value
For i = 1 To 12
Controls("txtUnitCoach" & i).Value = sh.Cells(f.Row, i + 14).Value
Next
Else
MsgBox "Not Found", vbExclamation
End If
Call UserForm_Initialize (This part works)
End Sub

Finally
I call the details populated into the corresponding fields in the Userform so I can make required changes to records and then my "Update" code uses "currentrow" to determine in the "Update" command code (Extract of Update Code)
Cells(currentrow, 1).Value = txtUnitNumber.Value
Cells(currentrow, 2).Value = cboUnitStockType.Value

What changes might I need to make to the update code to allow the search code you provided to update the original record, once the search code works.

Many thanks

I do not lknow if this help but I did a watch on the sh.Range("O:Z") variable and got the following responses
Value = <Out of Context>
Type = Empty

Also, the expected message box when an item is not found did not appear.

Thanks again

 

[DanteAmor]

I have entered your code and regretably I get no response,

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.