Search Multiple Columns

Dungadin

New Member
Joined
Mar 16, 2016
Messages
7
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:

BCDEFGHIJKLMNOPQRSTUVW
8LotSpaceSchedLastFirstIDGradeYearColorMakeModelLic. PlateYear2Color2Make2Model2Lic. plate2Year3Color3Make3Model3Lic. Plate3
9Blue4ERDoeJane12345122010BlueChevyTahoeABC12341988GreenDatsunB210ABC15982019YellowFordMustangSAM1410
10Blue77LSSmithSam78912112002WhiteHondaCivicFBO12342007GreyJeepCompassDBA09871968WhiteHyundaiSonataTUVXYM

<tbody>
</tbody>

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)

Code:
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,334
Office Version
365
Platform
Windows
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

New Member
Joined
Mar 16, 2016
Messages
7
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:

LotSpaceSchedLast NameFirst NameID#GradeYearColorMakeModelLic.Plate
Blue4DoeJane12345122010BlueChevyTahoeABC1234
Blue4DoeJane12345121988GreenDatsunB210ABC1598

<tbody>
</tbody>

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,134
Messages
5,484,932
Members
407,474
Latest member
Pam Sander

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top