Search Multiple Columns

[Dungadin]

I've searched the forum and have found similar topics, but none seem to intertwine with what I have, or I don't know how to insert it into the existing code. I feel like I'm close to figuring it out, but keep hitting a wall.

This is a parking lot database for my school. Each row is a parking space number, that can have up to 3 vehicles assigned to it.

I have 22 columns of data with 1000+rows beginning at B8. The first 7 columns are unique (space #, lot code, name, etc...). The remaining 15 are 3 duplicates sets of 5 columns (the vehicle information: year, make, model, color and lic plate).

While on patrol, I need to be able to search for a license plate or a model, make, or color of the vehicle. I'm using a userform that has a combobox that will identify which column to search, a textbox that you can input the data to search, and a listbox that will display the data it has searched for.

In regards to year, color, make, model and license plate, I need to be able to search 3 separated columns for the data. I've figured out the basic search for a singular column (the first 7 rows), but can't figure out how to incorporate searching 3 separated columns at one time.


Here's the basic layout of the data sheet start at B8:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Lot[/TD]
[TD]Space[/TD]
[TD]Sched[/TD]
[TD]Last[/TD]
[TD]First[/TD]
[TD]ID[/TD]
[TD]Grade[/TD]
[TD]Year[/TD]
[TD]Color[/TD]
[TD]Make[/TD]
[TD]Model[/TD]
[TD]Lic. Plate[/TD]
[TD]Year2[/TD]
[TD]Color2[/TD]
[TD]Make2[/TD]
[TD]Model2[/TD]
[TD]Lic. plate2[/TD]
[TD]Year3[/TD]
[TD]Color3[/TD]
[TD]Make3[/TD]
[TD]Model3[/TD]
[TD]Lic. Plate3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Blue[/TD]
[TD]4[/TD]
[TD]ER[/TD]
[TD]Doe[/TD]
[TD]Jane[/TD]
[TD]12345[/TD]
[TD]12[/TD]
[TD]2010[/TD]
[TD]Blue[/TD]
[TD]Chevy[/TD]
[TD]Tahoe[/TD]
[TD]ABC1234[/TD]
[TD]1988[/TD]
[TD]Green[/TD]
[TD]Datsun[/TD]
[TD]B210[/TD]
[TD]ABC1598[/TD]
[TD]2019[/TD]
[TD]Yellow[/TD]
[TD]Ford[/TD]
[TD]Mustang[/TD]
[TD]SAM1410[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Blue[/TD]
[TD]77[/TD]
[TD]LS[/TD]
[TD]Smith[/TD]
[TD]Sam[/TD]
[TD]78912[/TD]
[TD]11[/TD]
[TD]2002[/TD]
[TD]White[/TD]
[TD]Honda[/TD]
[TD]Civic[/TD]
[TD]FBO1234[/TD]
[TD]2007[/TD]
[TD]Grey[/TD]
[TD]Jeep[/TD]
[TD]Compass[/TD]
[TD]DBA0987[/TD]
[TD]1968[/TD]
[TD]White[/TD]
[TD]Hyundai[/TD]
[TD]Sonata[/TD]
[TD]TUVXYM[/TD]
[/TR]
</tbody>[/TABLE]

cboHeader is a combo box in the userform that lists the column headers
txtSearch is a text box in the userform where the user can input what they want to search for
lstEmployee is list box in userform
outdata is dynamic named range =OFFSET(Data!$AC$9,0,0,COUNTA(Data!$AC$9:$AC$9985),22)

Private Sub cmdContact_Click()
    Dim Crit As Range
    Dim FindMe As Range
    Dim DataSH As Worksheet

    On Error GoTo errHandler:
    Set DataSH = Sheet1
    Application.ScreenUpdating = False

    If Me.cboHeader.Value <> "All_Columns" Then
        If Me.txtSearch = "" Then
            DataSH.Range("AA9") = ""
        Else
            DataSH.Range("AA9") = "*" & Me.txtSearch.Value & "*"
        End If
    End If

    If Me.cboHeader.Value = "All_Columns" Then

        Set FindMe = DataSH.Range("B9:W30000").Find(What:=txtSearch, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)


        Set Crit = DataSH.Cells(8, FindMe.Column)


[INDENT]If Me.txtSearch = "" Then[/INDENT]
[INDENT]                DataSH.Range("AA9") = ""
                DataSH.Range("AA8") = ""
            Else
                DataSH.Range("AA8") = Crit
                If Crit = "ID" Then
                    DataSH.Range("AA9") = Me.txtSearch.Value
            Else
                    DataSH.Range("AA9") = "*" & Me.txtSearch.Value & "*"[/INDENT]
[INDENT]End If[/INDENT]


                Me.txtAllColumn = DataSH.Range("AA8").Value
            End If
    End If

[COLOR=#ff0000]' I was thinking of using "If" here; if AA8=Year, Color, Make, Model or Lic. Plate, then search corresponding Columns for string in AA9 ???????????[/COLOR]


    DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("Data!$AA$8:$AA$9"), CopyToRange:=Range("Data!$AC$8:$AX$8"), _
    Unique:=False

    lstEmployee.RowSource = DataSH.Range("outdata").Address(external:=True)

    On Error GoTo 0
    Exit Sub

errHandler:

    MsgBox "No match found for " & txtSearch.Text

    Me.lstEmployee.RowSource = ""

    Exit Sub
End Sub

Help me OB1, your my only hope

 

[Yongle]

It would anyone trying to help to see several typical examples of
- what to enter as search criteria
- what is the expected outcome for each search

Based on the 2 rows of data provided in post#1 ... please provide the following details for at least 6 different searches

Example 1
Search for :
Outcome :

Example 2
Search for :
Outcome :

etc ...

thanks

 

[Dungadin]

The way it is currently set up, the first 7 columns are taken care of.

On the user form, I select the column header in a combo box, enter what I want to search for in a text box. These 2 pieces are then entered into cells AA8 and AA9 respectively. The data in B:W are searched and copied to AC:AX, and that range is then copied to a list box on the userform, which can then be edited or changed.

Example:
Search for:

combobox (cboHeader): Space
textbox (txtSearch): 7​

Outcome:

Listbox (lstEmployee): All parking spaces that have a 7 and their subsequent data so with the above 2 rows, it would list all of Row 10
​

Search for:

cboHeader: License Plate
txtSearch: ABC​

Outcome:

lstEmployee: Row 9 and all other rows that have a Lic. plate that has "ABC" in it in columns M, R or W​

Search for:

cboHeader: Make
txtSearch: Chevy​

Outscome:

lstEmployee: Any row that has "Chevy" in columns K, P, U​

These last 2 examples would be for the 5 repeated columns for year, Color, Make, Model and Lic plate.

But the problem I see is if one row has 2 of the criteria, such as in the example row 9. It has 2 vehicles with Lic. plate "ABC". This would probably have to be separated into 2 lines in the list box, with the first 7 columns repeated for both. So it would read:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Lot[/TD]
[TD]Space[/TD]
[TD]Sched[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]ID#[/TD]
[TD]Grade[/TD]
[TD]Year[/TD]
[TD]Color[/TD]
[TD]Make[/TD]
[TD]Model[/TD]
[TD]Lic.Plate[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Doe[/TD]
[TD]Jane[/TD]
[TD]12345[/TD]
[TD]12[/TD]
[TD]2010[/TD]
[TD]Blue[/TD]
[TD]Chevy[/TD]
[TD]Tahoe[/TD]
[TD]ABC1234[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Doe[/TD]
[TD]Jane[/TD]
[TD]12345[/TD]
[TD]12[/TD]
[TD]1988[/TD]
[TD]Green[/TD]
[TD]Datsun[/TD]
[TD]B210[/TD]
[TD]ABC1598[/TD]
[/TR]
</tbody>[/TABLE]

What if we took the data from the primary table where each row is one space with 3 vehicles, created a second table that looked like the table above with each row only having 1 vehicle, and then searched the 2nd table for the data? That way, it would still only be searching a single column?

I would still need to be able to search the original table for the first 7 columns, because that lists all the vehicles assigned to the one space which can be edited in the userform.

I think I'd need a 2nd sub to copy the data from table 1 to table 2. Then in the sub here (cmdContact) put an If statement, that if the cboHeader = year, color, make, model, lic plate it would need to reference table 2, instead of table 1???

OR

Create a 2nd table which copies the date from table 1 into single rows like I described above. I could add another page (or tab) on the user form, that would be for searching vehicle data only.

OR

Add an additional listbox in the original user form, because the first listbox is set for 22 columns??

ARRRGHHHH, so frustrating.

wow.