Weird autofilter results
Posted by chrisB on February 08, 2002 9:02 AM
I am trying to autofilter all records that have 0 value in three fields. The code I am using is:
Selection.AutoFilter field:=17, Criteria1:="-", Operator:=xlAnd
Selection.AutoFilter field:=20, Criteria1:="-", Operator:=xlAnd
Selection.AutoFilter field:=21, Criteria1:="-", Operator:=xlAnd
The first filter works, the second and third return no data. When I use 0 as my criteria I get the same results - any ideas??
Posted by Chris D on February 08, 2002 12:38 PM
is this the same as 20876.html my earlier problem ?
Posted by chrisB on February 08, 2002 1:35 PM
Chris - it sure looks like it's the same - did you ever get resolution (didn't look like it when I read the posts)
Thanks
Posted by Chris D on February 09, 2002 3:35 AM
nope, but I think Mark was probably on the right track - I seem to remember something was odd about null, or zero values. Longterm, I'm going to have to figure it out as I do like that comma format. I'll try seeing its actions in other functions to see how it behaves, might give a clue
cheers !
Posted by Mark W. on February 09, 2002 3:51 PM
My conclusion was that AutoFilter considers
formatting; hence, after applying the Accounting
format, "-" is in its pick lists rather than the
internal representation, 0.
Posted by Mark W. on February 09, 2002 4:29 PM
Chris, I trust that you understand that you can't
use standard AutoFilters for an OR condition. If
you're testing...
field17="-" OR field20="-" OR field21="-"
You can't use standard AutoFilters, but must
employ an Advanced AutoFilter with a criteria
range defined on a worksheet.
