Autofilter question
in [Programming]
|
Prev: Open Multiple Files At Once
Next: Print all open files
From: Chad on 26 Apr 2010 16:23 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
From: Gord Dibben on 26 Apr 2010 17:03 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 <Chad(a)discussions.microsoft.com> 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
From: OssieMac on 26 Apr 2010 17:30 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
From: Gord Dibben on 26 Apr 2010 18:24 Ossie When I record, Excel adds the appropriate "=>60 days past due" Gord On Mon, 26 Apr 2010 14:30:01 -0700, OssieMac <OssieMac(a)discussions.microsoft.com> 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
From: OssieMac on 26 Apr 2010 18:43
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 <Chad(a)discussions.microsoft.com> > 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 > > . > |