AutoFilter Criteria in VBA

[Hilvert Scheper]

Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the Date in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Jacob Skaria]

Try the below

Sub Macro()

Dim dtStart As Date
Dim dtEnd As Date

dtStart = Workbooks("Other File.xls").Sheets("Sheet1").Range("A3")
dtEnd = Workbooks("Other File.xls").Sheets("Sheet1").Range("A9")

Selection.AutoFilter Field:=4, Criteria1:= _
">" & dtStart, Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<=" & dtEnd, Operator:=xlAnd
End Sub

If this post helps click Yes

 

[Per Jessen]

Hi

You are trying to filter for the text "'[Other File...." , not for the value
in A3. Look at this:

Set wb = Workbooks("Other File.xls")
Set sh = wb.Worksheets("Sheet1")
Crit1 = sh.Range("A3").Value
Crit2 = sh.Range("A9").Value
Selection.AutoFilter Field:=4, Criteria1:= _
">" & Crit1, _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<=" & Crit2, _
Operator:=xlAnd

Hopes this helps.

 

[Hilvert Scheper]

Dear Jacob and Per,
Thank You so much for Your ideas BUT...
The Filter still does not show anything; "0 Records of 1416 found", where it
should show 225 records...
Sorry!!
Any ideas Please?
Rgds, Hilvert

Hi

You are trying to filter for the text "'[Other File...." , not for the value
in A3. Look at this:

Set wb = Workbooks("Other File.xls")
Set sh = wb.Worksheets("Sheet1")
Crit1 = sh.Range("A3").Value
Crit2 = sh.Range("A9").Value
Selection.AutoFilter Field:=4, Criteria1:= _
">" & Crit1, _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<=" & Crit2, _
Operator:=xlAnd

Hopes this helps.
---
Per

Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the Date
in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Per Jessen]

Thanks for your reply,

Looking a bit closer to your filter statements, as you are trying to set up
two conditions for one column, it shall be done in one statement:

Selection.AutoFilter Field:=4, _
Criteria1:= ">" & Crit1, _
Operator:=xlAnd, _
Criteria2:= "<=" & Crit2

Hopes this helps.
---
Per

Dear Jacob and Per,
Thank You so much for Your ideas BUT...
The Filter still does not show anything; "0 Records of 1416 found", where
it
should show 225 records...
Sorry!!
Any ideas Please?
Rgds, Hilvert

Hi

You are trying to filter for the text "'[Other File...." , not for the
value
in A3. Look at this:

Set wb = Workbooks("Other File.xls")
Set sh = wb.Worksheets("Sheet1")
Crit1 = sh.Range("A3").Value
Crit2 = sh.Range("A9").Value
Selection.AutoFilter Field:=4, Criteria1:= _
">" & Crit1, _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<=" & Crit2, _
Operator:=xlAnd

Hopes this helps.
---
Per

Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the
Date
in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any
entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Patrick Molloy]

when using autofilter from the sheet data, you'll see dates in the dropdown
for values.
In code, when you select a date, you'll see the excel internal number. like
40008 for today

so in code you need to convert, eg

Option Explicit
Sub setFilter()
Dim sFilter As String
sFilter = ">=" & Format$(Workbooks("Book2").Worksheets(1).Range("C3"),
"dd/mm/yyyy")
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd
End Sub

NOTICE I used ">=" since there's no operator equivalent.

 

[Hilvert Scheper]

Dear Per and Jacob,

Many Thanks again to You Both for Your help,
Whatever I try, the filter Won't show the 225 entries that I need, "0
records found".
Very Frustrating, I think I'll just give up for now, looks like I'm not
getting anywhere with this.
Question; Why do You put ">" in the first Criteria (without the "="?), and
"<=" in the second, is there any logic to this? Just curious that's all.

Kind Regards,
Hilvert

Thanks for your reply,

Looking a bit closer to your filter statements, as you are trying to set up
two conditions for one column, it shall be done in one statement:

Selection.AutoFilter Field:=4, _
Criteria1:= ">" & Crit1, _
Operator:=xlAnd, _
Criteria2:= "<=" & Crit2

Hopes this helps.
---
Per

Dear Jacob and Per,
Thank You so much for Your ideas BUT...
The Filter still does not show anything; "0 Records of 1416 found", where
it
should show 225 records...
Sorry!!
Any ideas Please?
Rgds, Hilvert

Hi

You are trying to filter for the text "'[Other File...." , not for the
value
in A3. Look at this:

Set wb = Workbooks("Other File.xls")
Set sh = wb.Worksheets("Sheet1")
Crit1 = sh.Range("A3").Value
Crit2 = sh.Range("A9").Value
Selection.AutoFilter Field:=4, Criteria1:= _
">" & Crit1, _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<=" & Crit2, _
Operator:=xlAnd

Hopes this helps.
---
Per

"Hilvert Scheper" <[email protected]> skrev i
meddelelsen Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the
Date
in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any
entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Jacob Skaria]

Ok. Let us try this..

1. In a new workbook enter dummy data in ColA and B as below. Make sure the
dates are in excel date format. Shortcut to assign todays date (Ctrl+. In
cell C2 and D2 you have start and end dates..

ColA ColB ColC ColD
Date Day StartDate EndDate
7/1/2009 1 7/2/2009 7/7/2009
7/2/2009 2
7/3/2009 3
7/4/2009 4
7/5/2009 5
7/6/2009 6
7/7/2009 7
7/8/2009 8
7/9/2009 9
7/10/2009 10


2. Launch VBE using Alt+F11. Insert a module and paste the below code..

Sub Macro()
Dim dtStart As Date, dtEnd As Date
dtStart = Range("D1")
dtEnd = Range("E1")
Selection.AutoFilter Field:=1, _
Criteria1:=">" & dtStart, Operator:=xlAnd, _
Criteria2:="<=" & dtEnd
End Sub

3. Select columns A and B and run the macro to see what happens....It should
filter column 1 to display dates between start date and end date.

PS: ">" and "<" signs are there in the code which you pasted which denot
greater than and less than,..


If this post helps click Yes
---------------
Jacob Skaria

Dear Per and Jacob,

Many Thanks again to You Both for Your help,
Whatever I try, the filter Won't show the 225 entries that I need, "0
records found".
Very Frustrating, I think I'll just give up for now, looks like I'm not
getting anywhere with this.
Question; Why do You put ">" in the first Criteria (without the "="?), and
"<=" in the second, is there any logic to this? Just curious that's all.

Kind Regards,
Hilvert

Thanks for your reply,

Looking a bit closer to your filter statements, as you are trying to set up
two conditions for one column, it shall be done in one statement:

Selection.AutoFilter Field:=4, _
Criteria1:= ">" & Crit1, _
Operator:=xlAnd, _
Criteria2:= "<=" & Crit2

Hopes this helps.
---
Per

Dear Jacob and Per,
Thank You so much for Your ideas BUT...
The Filter still does not show anything; "0 Records of 1416 found", where
it
should show 225 records...
Sorry!!
Any ideas Please?
Rgds, Hilvert


:

Hi

You are trying to filter for the text "'[Other File...." , not for the
value
in A3. Look at this:

Set wb = Workbooks("Other File.xls")
Set sh = wb.Worksheets("Sheet1")
Crit1 = sh.Range("A3").Value
Crit2 = sh.Range("A9").Value
Selection.AutoFilter Field:=4, Criteria1:= _
">" & Crit1, _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<=" & Crit2, _
Operator:=xlAnd

Hopes this helps.
---
Per

"Hilvert Scheper" <[email protected]> skrev i
meddelelsen Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the
Date
in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any
entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Hilvert Scheper]

Dear Patrick,
Also to You a Huge Thanks for Your interference, I have tried Your suggestion
However Whetever I try, Nothing seems to work.
I just give up for now, can't afford to spend more time on this than I
already have, pretty close to a full day and not getting anywhere.

Many Thanks, and SORRY!
Hilvert

when using autofilter from the sheet data, you'll see dates in the dropdown
for values.
In code, when you select a date, you'll see the excel internal number. like
40008 for today

so in code you need to convert, eg

Option Explicit
Sub setFilter()
Dim sFilter As String
sFilter = ">=" & Format$(Workbooks("Book2").Worksheets(1).Range("C3"),
"dd/mm/yyyy")
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd
End Sub

NOTICE I used ">=" since there's no operator equivalent.

Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the Date
in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Jacob Skaria]

Hilvert; I dont think you have tried the code which I posted initially where
the variables are declared as *** Date ****


Please note the correction in references ....

Sub Macro()
Dim dtStart As Date, dtEnd As Date
dtStart = Range("C2")
dtEnd = Range("D2")
Selection.AutoFilter Field:=1, _
Criteria1:=">" & dtStart, Operator:=xlAnd, _
Criteria2:="<=" & dtEnd
End Sub

If this post helps click Yes
---------------
Jacob Skaria

Dear Per and Jacob,

Many Thanks again to You Both for Your help,
Whatever I try, the filter Won't show the 225 entries that I need, "0
records found".
Very Frustrating, I think I'll just give up for now, looks like I'm not
getting anywhere with this.
Question; Why do You put ">" in the first Criteria (without the "="?), and
"<=" in the second, is there any logic to this? Just curious that's all.

Kind Regards,
Hilvert

Thanks for your reply,

Looking a bit closer to your filter statements, as you are trying to set up
two conditions for one column, it shall be done in one statement:

Selection.AutoFilter Field:=4, _
Criteria1:= ">" & Crit1, _
Operator:=xlAnd, _
Criteria2:= "<=" & Crit2

Hopes this helps.
---
Per

Dear Jacob and Per,
Thank You so much for Your ideas BUT...
The Filter still does not show anything; "0 Records of 1416 found", where
it
should show 225 records...
Sorry!!
Any ideas Please?
Rgds, Hilvert


:

Hi

You are trying to filter for the text "'[Other File...." , not for the
value
in A3. Look at this:

Set wb = Workbooks("Other File.xls")
Set sh = wb.Worksheets("Sheet1")
Crit1 = sh.Range("A3").Value
Crit2 = sh.Range("A9").Value
Selection.AutoFilter Field:=4, Criteria1:= _
">" & Crit1, _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<=" & Crit2, _
Operator:=xlAnd

Hopes this helps.
---
Per

"Hilvert Scheper" <[email protected]> skrev i
meddelelsen Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the
Date
in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any
entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Patrick Molloy]

if you'd like to send me two excel workbooks , i'll see what i can do

Dear Patrick,
Also to You a Huge Thanks for Your interference, I have tried Your
suggestion
However Whetever I try, Nothing seems to work.
I just give up for now, can't afford to spend more time on this than I
already have, pretty close to a full day and not getting anywhere.

Many Thanks, and SORRY!
Hilvert

when using autofilter from the sheet data, you'll see dates in the
dropdown
for values.
In code, when you select a date, you'll see the excel internal number.
like
40008 for today

so in code you need to convert, eg

Option Explicit
Sub setFilter()
Dim sFilter As String
sFilter = ">=" &
Format$(Workbooks("Book2").Worksheets(1).Range("C3"),
"dd/mm/yyyy")
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd
End Sub

NOTICE I used ">=" since there's no operator equivalent.

Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the
Date
in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any
entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Dave Peterson]

Saved from a previous post:

This is from "Excel 2002 VBA Programmer's Reference"
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg

http://www.oaltd.co.uk:80/ExcelProgRef/Ch22/ProgRefCh22.htm
Search for "Range.AutoFilter" and you'll see this note:

Range.AutoFilter

The AutoFilter method of a Range object is a very curious beast. We are forced
to pass it strings for its filter criteria and hence must be aware of its string
handling behaviour. The criteria string consists of an operator (=, >, <, >=
etc.) followed by a value.

If no operator is specified, the "=" operator is assumed. The key issue is that
when using the "=" operator, AutoFilter performs a textual match, while using
any other operator results in a match by value. This gives us problems when
trying to locate exact matches for dates and numbers.

If we use "=", Excel matches on the text that is displayed in the cell, i.e. the
formatted number. As the text displayed in a cell will change with different
regional settings and Windows language version, it is impossible for us to
create a criteria string that will locate an exact match in all locales.

There is a workaround for this problem. When using any of the other filter
criteria, Excel plays by the rules and interprets the criteria string according
to US formats. Hence, a search criterion of ">=02/01/2001" will find all dates
on or after 1st Feb, 2001, in all locales.

We can use this to match an exact date by using two AutoFilter criteria. The
following code will give an exact match on 1st Feb, 2001 and will work in any
locale:

Range("A1200").AutoFilter 2, ">=02/01/2001", xlAnd, "<=02/01/2001"

==========

So you may want something like:

Selection.AutoFilter Field:=4, _
Criteria1:=">=" & format(startFilter, "mm/dd/yyyy")
Operator:=xlAnd, _
Criteria2:="<" & format(endFilter, "mm/dd/yyyy")

Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the Date in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Ron de Bruin]

Important: Use always the US mm/dd/yyyy format if you filter Dates.
Note: You only have the use the mm/dd/yyyy format in the code, no problem
if the format in the worksheet is different.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

if you'd like to send me two excel workbooks , i'll see what i can do

Dear Patrick,
Also to You a Huge Thanks for Your interference, I have tried Your
suggestion
However Whetever I try, Nothing seems to work.
I just give up for now, can't afford to spend more time on this than I
already have, pretty close to a full day and not getting anywhere.

Many Thanks, and SORRY!
Hilvert

when using autofilter from the sheet data, you'll see dates in the
dropdown
for values.
In code, when you select a date, you'll see the excel internal number.
like
40008 for today

so in code you need to convert, eg

Option Explicit
Sub setFilter()
Dim sFilter As String
sFilter = ">=" &
Format$(Workbooks("Book2").Worksheets(1).Range("C3"),
"dd/mm/yyyy")
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd
End Sub

NOTICE I used ">=" since there's no operator equivalent.



message Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values in
another workbook: Greater than the Date in Cell A3 and Less than the
Date
in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any
entries...
I Really appreciate Your help!!
Rgds, Hilvert

 

[Patrick Molloy]

hmm .... UK date format worked fine for me.

Important: Use always the US mm/dd/yyyy format if you filter Dates.
Note: You only have the use the mm/dd/yyyy format in the code, no problem
if the format in the worksheet is different.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

if you'd like to send me two excel workbooks , i'll see what i can do

Dear Patrick,
Also to You a Huge Thanks for Your interference, I have tried Your
suggestion
However Whetever I try, Nothing seems to work.
I just give up for now, can't afford to spend more time on this than I
already have, pretty close to a full day and not getting anywhere.

Many Thanks, and SORRY!
Hilvert


:

when using autofilter from the sheet data, you'll see dates in the
dropdown
for values.
In code, when you select a date, you'll see the excel internal number.
like
40008 for today

so in code you need to convert, eg

Option Explicit
Sub setFilter()
Dim sFilter As String
sFilter = ">=" &
Format$(Workbooks("Book2").Worksheets(1).Range("C3"),
"dd/mm/yyyy")
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=sFilter, Operator:=xlAnd
End Sub

NOTICE I used ">=" since there's no operator equivalent.



message Hi There,
Can Anyone help me out here Please,
I want to Filter a spreadsheet by Two criteria that refer to values
in
another workbook: Greater than the Date in Cell A3 and Less than the
Date
in
Cell A9.
How on earth can I do this? I tried:

Selection.AutoFilter Field:=4, Criteria1:= _
">'[Other File.xls]Sheet1'!$A$3", _
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:= _
"<='[Other File.xls]Sheet1'!$A$9", _
Operator:=xlAnd

and this doesn't work. The Filter is on but isn't showing Any
entries...
I Really appreciate Your help!!
Rgds, Hilvert