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

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

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

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
'<<====================


---
Regards,
Norman



"CC_rider" wrote in message
...
Any ideals on how I would go about setting up a macro to allow me to input
search criteria into a data filter ?

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

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"),valu e,_Operator:=xlAnd
.AutoFilter Field:=2, Criteria1:="=4", Operator:=xlOr, _
Criteria2:="10"

"Norman Jones" wrote:

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
'<<====================


---
Regards,
Norman



"CC_rider" wrote in message
...
Any ideals on how I would go about setting up a macro to allow me to input
search criteria into a data filter ?

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

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.


---
Regards,
Norman



"CC_rider" wrote in message
...
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"),valu e,_Operator:=xlAnd
.AutoFilter Field:=2, Criteria1:="=4", Operator:=xlOr, _
Criteria2:="10"

"Norman Jones" wrote:

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
'<<====================


---
Regards,
Norman



"CC_rider" wrote in message
...
Any ideals on how I would go about setting up a macro to allow me to
input
search criteria into a data filter ?

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

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 )

"Norman Jones" wrote:

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.


---
Regards,
Norman



"CC_rider" wrote in message
...
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"),valu e,_Operator:=xlAnd
.AutoFilter Field:=2, Criteria1:="=4", Operator:=xlOr, _
Criteria2:="10"

"Norman Jones" wrote:

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
'<<====================


---
Regards,
Norman



"CC_rider" wrote in message
...
Any ideals on how I would go about setting up a macro to allow me to
input
search criteria into a data filter ?

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

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.


---
Regards,
Norman



"CC_rider" wrote in message
...
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 )

"Norman Jones" wrote:

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.


---
Regards,
Norman



"CC_rider" wrote in message
...
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"),valu e,_Operator:=xlAnd
.AutoFilter Field:=2, Criteria1:="=4", Operator:=xlOr, _
Criteria2:="10"

"Norman Jones" wrote:

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
'<<====================


---
Regards,
Norman



"CC_rider" wrote in message
...
Any ideals on how I would go about setting up a macro to allow me to
input
search criteria into a data filter ?