Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel! How can I use a variable in an autofilter in VBA?
I want to use a variable as the criteria for an autofilter.
What I have is this: ActiveSheet.Range("$A$1:$S$277").AutoFilter Field:=1, Criteria1:="=StartDate", Operator:=xlAnd, Criteria2:="<=EndDate" where StartDate and EndDate would be assigned date values to filter between those dates. Results are no records as the variable name is used instead of the value of the variable. Variables are dim'ed as variants. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel! How can I use a variable in an autofilter in VBA?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel! How can I use a variable in an autofilter in VBA?
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/ExcelProgR...rogRefCh22.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("A1:D200").AutoFilter 2, "=02/01/2001", xlAnd, "<=02/01/2001" ============ So in your case: ActiveSheet.Range("$A$1:$S$277").AutoFilter Field:=1, _ Criteria1:="=" & format(StartDate,"mm/dd/yyyy"), _ Operator:=xlAnd, Criteria2:="<=" & format(EndDate,"mm/dd/yyyy") alskdjfhgzmxncbv wrote: I want to use a variable as the criteria for an autofilter. What I have is this: ActiveSheet.Range("$A$1:$S$277").AutoFilter Field:=1, Criteria1:="=StartDate", Operator:=xlAnd, Criteria2:="<=EndDate" where StartDate and EndDate would be assigned date values to filter between those dates. Results are no records as the variable name is used instead of the value of the variable. Variables are dim'ed as variants. Thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel! How can I use a variable in an autofilter in VBA?
That is 100% absolutly correct for what I wanted to do. Now my report works
exactly as I wanted. Thanks. "Nigel" wrote: Try Criteria1:="=" & StartDate -- Regards, Nigel "alskdjfhgzmxncbv" wrote in message ... I want to use a variable as the criteria for an autofilter. What I have is this: ActiveSheet.Range("$A$1:$S$277").AutoFilter Field:=1, Criteria1:="=StartDate", Operator:=xlAnd, Criteria2:="<=EndDate" where StartDate and EndDate would be assigned date values to filter between those dates. Results are no records as the variable name is used instead of the value of the variable. Variables are dim'ed as variants. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
autofilter with variable criteria | Excel Programming | |||
Variable in AutoFilter criteria | Excel Programming |