Help on AutoFilter

[Gio]

In autofilter method (expression.AutoFilter(Field, Criteria1, Operator,
Criteria2, VisibleDropDown)), Criteria1 is Optional Variant. The
criteria (a string; for example, "101")

Problem I encountered:
Selection.AutoFilter Field:=2, Criteria1:=date_limit, Operator:=xlAnd

where date_limit is a string variable.

But I always get Run-time error "1004". Autofilter method of Range
class failed.

Question:
Can I use a string variable instead of a direct string value like
"101"?

What am I doing wrong?

 

[Bob Phillips]

Gio,

It is not you, it is the way Autofilter (does not!) work. To my mind, as the
target cells are dates, you should be able to use a date, but Autofilter
works on strings, so you think okay let's create a date string, but this is
where vthe problem starts.

The obvious solution is to try and format the date in the same way as the
data in the list, like so

Dim string_value As String
string_value = "01-Jan-2005"
Selection.AutoFilter Field:=1, _
Criteria1:=Format(string_value, "dd-mmm")

but this does not work.

Believe it or not, this kludge does work

Dim string_value As String
string_value = "01-Jan-2005"
Selection.AutoFilter Field:=1, _
Criteria1:=">=" & Format(string_value, "dd-mmm"), _
Operator:=xlAnd, _
Criteria2:="<=" & Format(string_value, "dd-mmm")


--

HTH

RP
(remove nothere from the email address if mailing direct)