need macro\method that would allow user input to a data filter

C

CC_rider

Any ideals on how I would go about setting up a macro to allow me to input
search criteria into a data filter ?
 
N

Norman Jones

Hi CC_rider,

Perhaps something like:

'====================>>
Public Sub Tester01()
Dim rng As Range

Set rng = ActiveSheet.AutoFilter.Range

With rng
.AutoFilter Field:=1, Criteria1:=">ABC", Operator:=xlAnd
.AutoFilter Field:=2, Criteria1:="=4", Operator:=xlOr, _
Criteria2:=">10"
End With
End Sub
'<<====================
 
C

CC_rider

THX NJ (much love)-
What you gave me got me started but I'm only half way there ...
I need to be able reference a cell from "another sheet" to use as criteria
for the autofilter field:1.So I changed what you gave me (below) but it
still doesn't work ...Is the syntax wrong or something ?!?

Windows("Sheet1").Activate

Dim rng As Range

Set rng = ActiveSheet.AutoFilter.Range

With rng
.AutoFilter Field:=1,
Criteria1:=sheets("anothersheet").Range("a1"),value,_Operator:=xlAnd
.AutoFilter Field:=2, Criteria1:="=4", Operator:=xlOr, _
Criteria2:=">10"
 
N

Norman Jones

Hi CC_rider

There are a couple of syntax errors in your code.

Additionally, it is unnecessary to make selecttions here.

Try instead, something like:

'=======================>>
Sub Tester02()
Dim WB As Workbook
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim rng As Range
Dim sStr As String

Set WB = ActiveWorkbook '<==== CHANGE
Set SH1 = WB.Sheets("Sheet1") '<==== CHANGE
Set SH2 = WB.Sheets("Sheet3") '<==== CHANGE

sStr = SH1.Range("a1").Value '<==== CHANGE

Set rng = SH2.AutoFilter.Range

With rng
.AutoFilter Field:=1, Criteria1:=sStr, Operator:=xlAnd
.AutoFilter Field:=2, Criteria1:="=4", Operator:=xlOr, _
Criteria2:=">10"
End With

End Sub
'<<=======================

Change the indicated values to accord with your own data.
 
C

CC_rider

NJ - I meant to say.."...I need to reference a cell in another WORKBOOK
(file) not sheet " ...(pls bear w\me-THX)

P.S. To make it easier for me to deciper could you use my filenames in the
macro : (1st) "Daily purchase parts list.xls" (file that has the auto filter)
and (2nd)"Pump tester.xls" ( file that will contain the macro and also the
cell reference for the criteria needed in the auto filter )
 
N

Norman Jones

Hi CC_rider,

Try:

'=================>>
Sub Tester02A()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim rng As Range
Dim sStr As String

Set WB1 = Workbooks("Daily purchase parts list.xls")
Set WB2 = Workbooks("Pump tester.xls")

Set SH1 = WB2.Sheets("Sheet1") '<== CHANGE Sheet name
Set SH2 = WB1.Sheets("Sheet3") '<== CHANGE sheet name

sStr = SH1.Range("a1").Value '<<== CHANGE cell reference

Set rng = SH2.AutoFilter.Range

With rng
.AutoFilter Field:=1, Criteria1:=sStr, Operator:=xlAnd
.AutoFilter Field:=2, Criteria1:="=4", Operator:=xlOr, _
Criteria2:=">10"
End With
End Sub

'<<=================

Change the two sheet names to match your actual sheet names. Change the cell
reference also.
 
D

dtg_denver

Norman,

Hope you don't mind me busting into this thread. I used part of the code you
posted here, and it works great. What I"m now trying to figure out is how to
input in C3 a part of the string. Example: search for 123 would return
1234565, 41235, ...

Here is the code I'm using: THX

Sub searchvin()
Dim WB As Workbook
Dim SH1 As Worksheet
Dim rng As Range
Dim sStr As String

Set WB = ActiveWorkbook '<==== CHANGE
Set SH1 = WB.Sheets("Master") '<==== CHANGE

sStr = SH1.Range("c3").Value '<==== CHANGE

Set rng = SH1.AutoFilter.Range

With rng
.AutoFilter Field:=4, Criteria1:=sStr, Operator:=xlAnd

End With

End Sub
 
Top