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:
<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)
Help me OB1, your my only hope
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 |
<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