Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba autofilter problem
I have the below code:
With Sheets("Stores") .Cells.AutoFilter Field:=1, Criteria1:=Sheets("Worksheet").Range("C1").Value .Cells.AutoFilter Field:=4, Criteria1:=Sheets("Worksheet").Range("Q2").Value End With The first autofilter works fine. The 2nd doesn't. Field 4 is a column (D) of dates. Worksheet.Range("Q2") is a date. The cells are formatted the same. I can find a cell in column D with the same value as worksheet.range("Q2") and use an If statement to confirm the two values are equal. ??? -- Thanks Shawn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba autofilter problem
Autofilter uses a string when comparing and the value number of a cell with a date is a number. The number will not match the string. the best solution is this From : Cells.AutoFilter Field:=4, Criteria1:=Sheets("Worksheet").Range("Q2").Value To : DateStr = format(Sheets("Worksheet").Range("Q2").Value,"mm-dd-yy") Cells.AutoFilter Field:=4, Criteria1:=DateStr The "mm-dd-yy" must match the format of the data in the worksheet. Change the format as required. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165290 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba autofilter problem
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/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 you may want something like this for July of 2009: Dim myStr as string .... mystr = format(workSheets("Worksheet").Range("Q2").Value, "mm/dd/yyyy") .... .Cells.AutoFilter Field:=4, Criteria1:="=" & mystr, _ operator:=xland, criteria2:="<=" & mystr Shawn wrote: I have the below code: With Sheets("Stores") .Cells.AutoFilter Field:=1, Criteria1:=Sheets("Worksheet").Range("C1").Value .Cells.AutoFilter Field:=4, Criteria1:=Sheets("Worksheet").Range("Q2").Value End With The first autofilter works fine. The 2nd doesn't. Field 4 is a column (D) of dates. Worksheet.Range("Q2") is a date. The cells are formatted the same. I can find a cell in column D with the same value as worksheet.range("Q2") and use an If statement to confirm the two values are equal. ??? -- Thanks Shawn -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter problem | Excel Discussion (Misc queries) | |||
Problem with AutoFilter | Excel Programming | |||
Problem using Autofilter | Excel Programming | |||
AutoFilter VBA Problem | Excel Discussion (Misc queries) | |||
AutoFilter Problem | Excel Programming |