Search Multiple Columns
Results 1 to 3 of 3

Thread: Search Multiple Columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2016
    Location
    Phoenix, Az.
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search Multiple Columns

    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:

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

    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)
    
    
    
    If Me.txtSearch = "" Then
    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 & "*"
    End If
    Me.txtAllColumn = DataSH.Range("AA8").Value End If End If ' I was thinking of using "If" here; if AA8=Year, Color, Make, Model or Lic. Plate, then search corresponding Columns for string in AA9 ??????????? 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

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,613
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Search Multiple Columns

    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

  3. #3
    New Member
    Join Date
    Mar 2016
    Location
    Phoenix, Az.
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search Multiple Columns

    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:

    Lot Space Sched Last Name First Name ID# Grade Year Color Make Model Lic.Plate
    Blue 4 Doe Jane 12345 12 2010 Blue Chevy Tahoe ABC1234
    Blue 4 Doe Jane 12345 12 1988 Green Datsun B210 ABC1598

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •