Autofilter - Applying changes

[Nigel]

Hi All
I am using an autofilter under VBA control. To set up the customer
conditions I record the macro and adapt the code as required.

Manually the filter works ok, and the resulting code is created and the
filter acts immediately on the datalist showing only those meeting the
criteria specified. The filter relate to some date ranges shown on the sheet
in European format.

If I remove the filter and run the recorded macro, the filter is put in
place, the Custom conditions are set BUT all rows are filtered. If I open
the filter to check the criteria settings, they are as requested by the
macro. If I then click OK on the filter the resultant list is filtered
correctly with conditions being applied correctly !!

I suspect it is to do with the DATES - but cannot reconcile the difference
between the Manual application and the VBA macro? Thoughts anyone?

Using Xl97 (v8) as the target application, but I get the same in xlXP (v10).

Recorded (unedited code below)

Range("A4:C4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=">=19/10/2005", Operator:=xlAnd
_
, Criteria2:=">=15/11/2005"

 

[Tom Ogilvy]

try

Range("A4:C4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=">=" & DateSerial(2005,10,19), _
Operator:=xlAnd, Criteria2:=">=" & DateSerial(2005,11,15)

 

[Nigel]

Hi All,
In my haste to show the recorded code I set the custom conditions
incorrectly - they should read...

Range("A4:C4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=">=19/10/2005", Operator:=xlAnd _
, Criteria2:="<=15/11/2005"

 

[Nigel]

Hi Tom
I tried that earlier but re-applied your suggestion again - without success.
I still see the customer conditions set correctly but NO list, unless I
review the filter manually and press OK.
It is really weird!

 

[Nigel]

Hi Tom
I have continued to investigate. It is definitely the date format that is
causing the problem. If I set the dates on the worksheet to US format and
set the filter conditional to the same it works. So it appears that
running the filter manually Excel will resolve the date based on local
settings. But VBA uses the US form of dates so when that is applied, the
sheet is has an incompatible format. Similar problem to userform control of
dates.
My problem remains, as I cannot figure out the conversion process, it seems
VBA always uses US format dates. Any ideas as a workaround?

 

[Nigel]

OK I have cracked it, I am using the following.......

Criteria1:=">" & Format("13/10/2005", "mm/dd/yy")

 

[Norman Jones]

Hi Nigel,

The following version worked for me:

Sub Tester()

Range("A4:C4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, _
Criteria1:=">=" & CLng(DateSerial(2005, 10, 19)), _
Operator:=xlAnd, _
Criteria2:="<=" & CLng(DateSerial(2005, 11, 15))
End Sub

 

[Nigel]

Thanks, Norman. I will try this.

 

[Tom Ogilvy]

Norman,
Just for clarification (I don't have this problem or have a way to test it),
you must include the clng - just the dateserial doesn't work?
( as I recall you are using a Non-US version of Excel)

 

[Norman Jones]

Hi Tom,

If I had UK regional settings (i.e. with a dd/mm/yy) format, I was able to
reproduce the results reported by Nigel. adiding the explicit Clng
conversion resolved the problem - for me!

If I used US regional settings (with conventional mm/dd/yy dates), your code
worked for me without alteration.

The point of difference between my suggestion and the resoltion adopted by
Nigel, is that the use of the Clng conversion worked whichever of the two
regional settings I employed.

As you correctly surmise, I am using a UK version and I cannot comment for
other versions.

 

[Tom Ogilvy]

Have worked this in the past with someone in the UK, but quite a while ago
and couldn't remember whether the clng was required. I rememberd the
solution put the serial number in the criteria box when viewed manually
(rather than a date - so I guess I should have defaulted to the clng which I
use frequently with MATCH). Thanks for refreshing my memory.