Help on AutoFilter

G

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?
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Automated autofilter error 1
Autofilter problem 9
Autofilter in a macro 1
Help me4 2
Apply macro to all worksheets 15
Custom filter 26
Problems with AutoFilter macro 2
How to Activate Filter in Protected Sheet 2

Top