How to filter based on date?

[XLstooge]

Hi newbie here,
I don't have any macro knowledge but I am trying to create a macro that filters transaction records based on their aging days.
I need to filter transaction older than 2 months, 3 months, and so on. I don't know if it is possible to put the aging calculation on macro but I tried to calculate by excel worksheet formula and copy its value to my macro, no success.
Would somebody please give some knowledge. Been working on this for 4 days.

 

[Beate Schmitz]

Hello,

This would be your sheet:

Excel Workbook
	A	B
1	Criteria:	Month ago:
2	5/23/2009	2
3	*	*
4	*	*
5	*	*
6	Date	*
7	7/13/2009	*
8	7/18/2009	*
9	3/18/2009	*
10	6/13/2009	*
11	3/28/2009	*
12	4/16/2009	*
Sheet

Put formula in A2 and number of months to be filtered back from today in B2. Autofilter is in cell A6.


Then you can use this code:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><pre><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#000080"; >Sub</span> Test()
Selection.AutoFilter Field:=1, Criteria1:=<span style="color:#800000"; >">"</span> & CDbl(Range(<span style="color:#800000"; >"A2"</span>).Value)
<span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></span></pre></div>

By changing cell B2 the autofilter-code will show different results depending on the value of this cell.

Respectively, if you only want to show dates more than e.g. 2 months ago, the operator in the code has to be amended:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><pre><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#000080"; >Sub</span> Test2()
Selection.AutoFilter Field:=1, Criteria1:=<span style="color:#800000"; >"<"</span> & CDbl(Range(<span style="color:#800000"; >"A2"</span>).Value)
<span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span></span></pre></div>

 

[XLstooge]

Beate, thank you for quick response.
Can you explain what does this syntax do:
"CDbl(Range("A2").Value)" ?

I am still working on this using this macro:

Dim myDate As Date
myDate = CLng(Date) - 60
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="<" & myDate, Operator:=xlAnd

I think I'm getting good result filtering 2 months old transaction although I'm not sure what's this "Operator:=xlAnd" does.

 

[Beate Schmitz]

Hello,

you don't need the "Operator:=xlAnd" because you only filter for one criteria.

By the way, have you tested my example? The advantage is, that you can easily change the time frame by entering a different value in B2.

To understand CDbl search for "type conversion function". It is too difficult for me to explain it in English.

 

[XLstooge]

Beate,
Thank you very much. I tried your method, it sure does have the luxury to change aging period as easy as typing a simple number!