Filter Date Field by month using VBA

S

Stuart Grant

I have a list with transactions. The first field is Date (maybe that's not
a good name) and I want to filter the records by month.

I tried this
Mon = Val(InputBox("Which month"))
Selection.AutoFilter Field:=1 Criteria:= "Month(Field 1) = Mon"
but this gives a syntax error as do several variations which I have tried,
although Month(Date) is apparently a VBA function.

Can anyone suggest the right syntax ?

Stuart
 
A

Ardus Petus

I don't think you can do that with autofilter.
You'll have to use an advanced filter with a critaria range

HTH
 
R

Roger Govier

Hi

Autofilter will not work like that, it needs to use the date range for
the month combined with 2 criteria "And'd" together.
You will need to convert your month input into the two date to be used
for Criter1a1 and Criteria2

Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=01/01/2006",
Operator:=xlAnd _
, Criteria2:="<=31/01/2006"

Alternatively, on the Sheet of data, add an extra column, say column J,
with the formula =MONTH(A1) copied down and then apply your single
filter to that new field

Mon = Val(InputBox("Which month"))
Selection.AutoFilter Field:=10 Criteria:= Mon
 
S

Stuart Grant

Roger
Many thanks. Your alternative suggestion looked fine - very compact, but I
can't get it to work.
I made an extra Column I with =Month(A1) copied all the way down but
Selection.AutoFilter Field:= 9, Criteria:=Mon
gives Error 1004 - Application Defined or Object Defined Error - which I
always think is one of the most unhelpful messages around but never mind.
I have checked over and over for typos but found none. I think actually you
missed a comma but I tried leaving it out. No go.
Stuart

Roger Govier said:
Hi

Alternatively, on the Sheet of data, add an extra column, say column J,
with the formula =MONTH(A1) copied down and then apply your single filter
to that new field

Mon = Val(InputBox("Which month"))
Selection.AutoFilter Field:=10 Criteria:= Mon
Click to expand...
[/QUOTE]
 
R

Roger Govier

Hi Stuart
Do you have autofilter applied to all columns from A to I?
If not, count the position of the autofilter for column I and substitute
in place of 9 in the code.
Check that Mon is an integer value between 1 and 12.

--
Regards

Roger Govier


Stuart Grant said:
Roger
Many thanks. Your alternative suggestion looked fine - very compact,
but I can't get it to work.
I made an extra Column I with =Month(A1) copied all the way down but
Selection.AutoFilter Field:= 9, Criteria:=Mon
gives Error 1004 - Application Defined or Object Defined Error - which
I always think is one of the most unhelpful messages around but never
mind. I have checked over and over for typos but found none. I think
actually you missed a comma but I tried leaving it out. No go.
Stuart
Click to expand...
[/QUOTE]
 
S

Stuart Grant

Roger
Yes. autofilter is applied to all columns and the Month column is I or 9.
Debug-AddWatch confirms Mon is an integer - in my trials 2.
I'm baffled.
I have to stop now but this afternoon I'll record a macro doing it manually
and see what comes.
Stuart
 
S

Stuart Grant

Roger
Oh silly me! As you will see in my previous messages. I had -
Selection.autofilter Fields:= 9, Criteria = Mon
It has to be Criteria1 not just Criteria, even if there is only one.
Works perfectly now. Thanks for your help.
Stuart
 
R

Roger Govier

Hi Stuart

You're not the silly one - it was me.
In copying and pasting, I wiped out the 1 after criteria in my posting
to you.
Glad you spotted it and got it all resolved.
 
Top