Getting a macro to use a cell value as a custom autofilter 4

[sicohne]

I am trying to get my macro to take the date from a cell and autofilter a column so that it only shows cells before the given date.

I have tried:

range("M1&quot.select
ActiveCell.FormulaR1C1="=Today()"
a = range("M1&quot.value
b = &quot;<&quot; & a
selection.autofilter field:=6, criteria1:=b

When I run this, the autofilter returns nothing, but if you click on the arrow and select custom then click OK (the fields will already be set to &quot;Less than&quot; and today's date) a whole load of lines come up.

Am I missing something obvious?

 

[ETID]

try....


a = cstr(range(&quot;M1&quot)

 

[sicohne]

No joy, same result

 

[VBAjedi]

Your code is trying to filter range &quot;M1&quot; (that being the current selection). . . is that part of your data area? If so, why are you writing your filter criteria (date) inside your data area? Try explicitly stating your data area for the autofilter statement:

Worksheet(&quot;Sheet1&quot.Range(&quot;A2:M57&quot

Obviously, you need to put your own sheet name and range into that statement.

Post back and let us know what happens!

VBAjedi

 

[VBAjedi]

Last post was missing the autofilter code in my snippet. Should look like:

Worksheet(&quot;Sheet1&quot.Range(&quot;A2:M57&quot.autofilter field:=6, criteria1:=b


VBAjedi

 

[sicohne]

range &quot;m1&quot; is just the first available cell outside of the data area. The data area is A1:L(how ever many rows it goes down to). I'm just using M1 as somewhere to work out todays date and use it as the selection criteria for the autofilter.

 

[VBAjedi]

It's a bad idea to put anything in a row or column that is adjacent to an area you will be filtering. Excel gets confused about where the data ends.

Fortunately, you can bypass putting the date on the worksheet at all - just get it in the code. With that approach you only need two code lines:

b = &quot;<&quot; & Date
worksheets(&quot;MyWorksheetName&quot.Range(&quot;A1:L1000&quot.autofilter field:=6, criteria1:=b

If you still can't get that to work, the next thing to look at is your dates - does Excel actually recognize them as dates? Double-check your formatting on that column. . .

Keep posting back until you get it working!

VBAjedi

 

[sicohne]

same result, I even tried
b = CStr(&quot;<&quot; & Date)
but to no avail. The column is definently in date format.

 

[ETID]

Add a debug.print for variable a....
see if the code is returning a date as a string or as a timestamp


range(&quot;M1&quot.select
ActiveCell.FormulaR1C1=&quot;=Today()&quot;
a = range(&quot;M1&quot.value

'debug.print a
'----or---
'msgbox(a)
'either one will do

b = &quot;<&quot; & a
selection.autofilter field:=6, criteria1:=b

 

[sicohne]

According to the watch window, a is type Date & b is being returned as type String

 

[VBAjedi]

You have probably overlooked something simple here. Try hardcoding the value of &quot;b&quot; as follows:

b = &quot;<10/23/03&quot;
selection.autofilter field:=6, criteria1:=b

If that filters correctly, then you need to figure out how to get your &quot;a&quot; date value into that exact format. If it doesn't filter correctly you have a problem with your autofilter setup (probably either your selection area, or your field number). Have you tried what I suggested in my earlier post - explicitly stating your data area for the autofilter statement instead of relying on Excel's interpretation of &quot;Selection&quot;?


VBAjedi

 

[Adamba]

sicohne,

Looking at this when the autofilter assigns a date it uses the current format of the column (long/short/.. Date).
But..
When telling the autofilter that it should assign a date filter it needs to recieve the variable as a number or it cannot update the filter appropriatly until it can convert the date to the number version (clicking OK).

Try the following and it should work, i have tested this in Excel 97 only

Sub FilterCurrentDate()
Dim a
a = &quot;<&quot; & CLng(Date)
Selection.AutoFilter field:=6, Criteria1:=a
End Sub




Adam

 

[xlbo]

this is all about formatting - you can either do as Adamba suggests and convert the date to it's serial number OR you can pick up the formatting from the 1st row in the table and apply that:
syntax would be:

mDate = format(now(),cells(2,6).numberformat)
range(&quot;A1:Z1000&quot.autofilter field:=6, criteria1:=&quot;<&quot; & mDate

where A1:Z1000 is your data set

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sicohne]

Yup, it was the formatting. Using the CLng(date) made it work perfectly.
Thanks guys.