Filter Date Field by month using VBA

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

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

HTH
--
AP

"Stuart Grant" <sagrantatbluewindotch> a écrit dans le message de
news:[email protected]...
> 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
>
>

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

--
Regards

Roger Govier


"Stuart Grant" <sagrantatbluewindotch> wrote in message
news:[email protected]...
>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
>

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" <[email protected]> wrote in message
news:e2Ac%[email protected]...
> 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
>
> --
> Regards
>
> 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" <sagrantatbluewindotch> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:e2Ac%[email protected]...
>> 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
>>
>> --
>> Regards
>>
>> Roger Govier
>>>
>>
>>
>
>

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

"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> 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

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


"Stuart Grant" <sagrantatbluewindotch> wrote in message
news:[email protected]...
> 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

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.

--
Regards

Roger Govier


"Stuart Grant" <sagrantatbluewindotch> wrote in message
news:[email protected]...
> 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
>
>
> "Stuart Grant" <sagrantatbluewindotch> wrote in message
> news:[email protected]...
>> 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
>
>