Search using Autofilter

B

Ben Allen

I have a table with 11 headings. I have a form that contains option buttons
for each of these headings and a critera box. When the user clicks the
search button i need it to auto filter the desired heading by the critera
entered. This will then be copied to a new page called search results ( i
know how to do this bit). Thanks in advance for any help.
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"
 
E

Ed

Hi, Ben. This is code I use to AutoFilter my spreadsheet. I use an
InputBox to get the filter criteria. You would most likely want to use the
value of the text box as the string, and put your code in the
CommandButton_Click event.

HTH
Ed

' Clear previous sort
Sheets("Sheet1").AutoFilterMode = False

With Cells
.EntireColumn.Hidden = False
.EntireRow.Hidden = False
End With

Range("A1").Select

' Choose series by letter
MyTarget = Application.InputBox("Which TIR series?")

Application.ScreenUpdating = False

' Filter for report
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=L5-" & MyTarget & "*"
 
B

Ben Allen

Thanks for your help,
I have used IF functions for use with the checkboxes but get an error with
my current code. Can anyone help?
Thanks in advance, Ben

Private Sub SearchButton_Click()
Range("B3:L10").Select
'Set Field
Dim myField As Integer
If DateOp = True Then
Set myField = 1
ElseIf TourRef = True Then
Set myField = 2
ElseIf Country = True Then
Set myField = 3
ElseIf Place = True Then
Set myField = 4
ElseIf Spaces = True Then
Set myField = 10
End If
Selection.AutoFilter Field:=myFeild, Criteria1:=Search.Value,
Operator:=xlAnd

End Sub
 
Top