Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter... not quite right
I have a Inputbox i would like to use to set the AutoFilter criteria1. Then
display the results of that filter on the active worksheet. This is what i have so far... Sub Sort() Dim Message, Title, MyValue Message = "Please Enter A Date" Title = "Title" MyValue = InputBox(Message, Title) Range("Headers").Select Selection.AutoFilter Selection.AutoFilter Field:=5, Criteria1:=MyValue End Sub The code almost works, it select the headers, creates the auto filter but dosent display the results of the filter. the ultimate goal here is to only see the date entered on the active sheet to be printed. Any help would be great. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter... not quite right
VBA and autofiltering is pretty USA centric...
Instead of: Selection.AutoFilter Field:=5, Criteria1:=MyValue try: Selection.AutoFilter field:=2, _ criteria1:="=" & format(cdate(myvalue), "mm/dd/yyyy"), _ operator:=xlAnd, _ criteria2:="<="& format(cdate(myvalue),"mm/dd/yyyy") You may want to add some checks to see if the myValue was really a date, too. Some background: Ron de Bruin sent me this message in a private email a few months ago: See also Stephen his Autofilter notes in this PDF http://www.oaltd.co.uk/ExcelProgRef/...rogRefCh22.htm 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" Office_Novice wrote: I have a Inputbox i would like to use to set the AutoFilter criteria1. Then display the results of that filter on the active worksheet. This is what i have so far... Sub Sort() Dim Message, Title, MyValue Message = "Please Enter A Date" Title = "Title" MyValue = InputBox(Message, Title) Range("Headers").Select Selection.AutoFilter Selection.AutoFilter Field:=5, Criteria1:=MyValue End Sub The code almost works, it select the headers, creates the auto filter but dosent display the results of the filter. the ultimate goal here is to only see the date entered on the active sheet to be printed. Any help would be great. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter... not quite right
Thanks Dave, After a while of beating my head against the desk i tried
this... Works great. Private Sub Sort() Dim Message, Title, MyValue Dim ExactDate As Date Dim ExactCriterion As String Message = "Please Enter A Date, (mm/dd/yy)" Title = "Title" MyValue = InputBox(Message, Title) ExactCriterion = Format(ExactDate, "mm/dd/yy") Range("Headers").AutoFilter Range("Headers").AutoFilter Field:=5, Criteria1:=MyValue "Dave Peterson" wrote: VBA and autofiltering is pretty USA centric... Instead of: Selection.AutoFilter Field:=5, Criteria1:=MyValue try: Selection.AutoFilter field:=2, _ criteria1:="=" & format(cdate(myvalue), "mm/dd/yyyy"), _ operator:=xlAnd, _ criteria2:="<="& format(cdate(myvalue),"mm/dd/yyyy") You may want to add some checks to see if the myValue was really a date, too. Some background: Ron de Bruin sent me this message in a private email a few months ago: See also Stephen his Autofilter notes in this PDF http://www.oaltd.co.uk/ExcelProgRef/...rogRefCh22.htm 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" Office_Novice wrote: I have a Inputbox i would like to use to set the AutoFilter criteria1. Then display the results of that filter on the active worksheet. This is what i have so far... Sub Sort() Dim Message, Title, MyValue Message = "Please Enter A Date" Title = "Title" MyValue = InputBox(Message, Title) Range("Headers").Select Selection.AutoFilter Selection.AutoFilter Field:=5, Criteria1:=MyValue End Sub The code almost works, it select the headers, creates the auto filter but dosent display the results of the filter. the ultimate goal here is to only see the date entered on the active sheet to be printed. Any help would be great. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter... not quite right
First, I didn't notice this before, but I wouldn't use a name for my procedure
that was a method in excel's VBA. It may not confuse excel, but it might confuse me. And I don't see what your exactcriteria and exactdate variables are doing. Personally, I don't like to let excel guess my range to filter. I'd rather define the range explicitly. Office_Novice wrote: Thanks Dave, After a while of beating my head against the desk i tried this... Works great. Private Sub Sort() Dim Message, Title, MyValue Dim ExactDate As Date Dim ExactCriterion As String Message = "Please Enter A Date, (mm/dd/yy)" Title = "Title" MyValue = InputBox(Message, Title) ExactCriterion = Format(ExactDate, "mm/dd/yy") Range("Headers").AutoFilter Range("Headers").AutoFilter Field:=5, Criteria1:=MyValue "Dave Peterson" wrote: VBA and autofiltering is pretty USA centric... Instead of: Selection.AutoFilter Field:=5, Criteria1:=MyValue try: Selection.AutoFilter field:=2, _ criteria1:="=" & format(cdate(myvalue), "mm/dd/yyyy"), _ operator:=xlAnd, _ criteria2:="<="& format(cdate(myvalue),"mm/dd/yyyy") You may want to add some checks to see if the myValue was really a date, too. Some background: Ron de Bruin sent me this message in a private email a few months ago: See also Stephen his Autofilter notes in this PDF http://www.oaltd.co.uk/ExcelProgRef/...rogRefCh22.htm 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" Office_Novice wrote: I have a Inputbox i would like to use to set the AutoFilter criteria1. Then display the results of that filter on the active worksheet. This is what i have so far... Sub Sort() Dim Message, Title, MyValue Message = "Please Enter A Date" Title = "Title" MyValue = InputBox(Message, Title) Range("Headers").Select Selection.AutoFilter Selection.AutoFilter Field:=5, Criteria1:=MyValue End Sub The code almost works, it select the headers, creates the auto filter but dosent display the results of the filter. the ultimate goal here is to only see the date entered on the active sheet to be printed. Any help would be great. -- Dave Peterson -- Dave Peterson |
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) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |