Autofilter question
 

I have an autofilter with a column of data that includes operators such as
and <.

For example, one of the records would read " 60 days past due"

How would I go about coding the criteria to show the records that display "
60 days past due"?

Thanks,
Chad

Autofilter question
 

Macro recorder returns this.

Selection.AutoFilter Field:=1, Criteria1:="= 60 days past due"


Gord Dibben MS Excel MVP


On Mon, 26 Apr 2010 13:23:01 -0700, Chad
wrote:

I have an autofilter with a column of data that includes operators such as
and <.

For example, one of the records would read " 60 days past due"

How would I go about coding the criteria to show the records that display "
60 days past due"?

Thanks,
Chad

Autofilter question
 

Hi chad,

I am assuming that you tried recording this and while the filter worked in
the interactive mode; when you run the code it does not work. The answer is
to insert a leading equal sign.

"=60 days past due"

A tip for these things is the following code. Set the filter then run the
code and you will be able to see the criteria that needs to be used for the
code.

Dim strCriteria

With Worksheets("Sheet1")
If .AutoFilterMode Then
With .AutoFilter.Filters(2)
If .On Then strCriteria = .Criteria1
End With
End If
End With

MsgBox strCriteria


--
Regards,

OssieMac


"Chad" wrote:

I have an autofilter with a column of data that includes operators such as
and <.

For example, one of the records would read " 60 days past due"

How would I go about coding the criteria to show the records that display "
60 days past due"?

Thanks,
Chad

Autofilter question
 

Ossie

When I record, Excel adds the appropriate "=60 days past due"


Gord

On Mon, 26 Apr 2010 14:30:01 -0700, OssieMac
wrote:

Hi chad,

I am assuming that you tried recording this and while the filter worked in
the interactive mode; when you run the code it does not work. The answer is
to insert a leading equal sign.

"=60 days past due"

A tip for these things is the following code. Set the filter then run the
code and you will be able to see the criteria that needs to be used for the
code.

Dim strCriteria

With Worksheets("Sheet1")
If .AutoFilterMode Then
With .AutoFilter.Filters(2)
If .On Then strCriteria = .Criteria1
End With
End If
End With

MsgBox strCriteria

Autofilter question
 

In Excel 2007 the macro recorder does not return the equal sign. The
following is what I get with the macro recorder. I have not tested in any
other versions.

ActiveSheet.Range("$A$1:$B$835").AutoFilter Field:=2, Criteria1:= _
" 60 days past due"

And of course the above does not work without the leading = sign when the
code is run.

--
Regards,

OssieMac


"Gord Dibben" wrote:

Macro recorder returns this.

Selection.AutoFilter Field:=1, Criteria1:="= 60 days past due"


Gord Dibben MS Excel MVP


On Mon, 26 Apr 2010 13:23:01 -0700, Chad
wrote:

I have an autofilter with a column of data that includes operators such as
and <.

For example, one of the records would read " 60 days past due"

How would I go about coding the criteria to show the records that display "
60 days past due"?

Thanks,
Chad

.

Autofilter question
 

My testing was on 2003.

I have since tested on 2007 and found that you get different results
depending upon how you filter.

Selecting from the list you get

ActiveSheet.Range("$A$1:$A$22").AutoFilter Field:=1, Criteria1:="60 days"

Selecting from CustomEquals you get

ActiveSheet.Range("$A$1:$A$22").AutoFilter Field:=1, Criteria1:="=60 days"

2003 treats both methods the same and returns in both cases

ActiveSheet.Range("$A$1:$A$22").AutoFilter Field:=1, Criteria1:="=60 days"



Gord

On Mon, 26 Apr 2010 15:43:02 -0700, OssieMac
wrote:

In Excel 2007 the macro recorder does not return the equal sign. The
following is what I get with the macro recorder. I have not tested in any
other versions.

ActiveSheet.Range("$A$1:$B$835").AutoFilter Field:=2, Criteria1:= _
" 60 days past due"

And of course the above does not work without the leading = sign when the
code is run.