| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - Applying changes
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" -- Cheers Nigel |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
try
Range("A4:C4").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=" & DateSerial(2005,10,19), _ Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15) -- Regards, Tom Ogilvy "Nigel" wrote in message ... 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" -- Cheers Nigel |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
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" -- Cheers Nigel "Nigel" wrote in message ... 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" -- Cheers Nigel |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
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! -- Cheers Nigel "Tom Ogilvy" wrote in message ... try Range("A4:C4").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=" & DateSerial(2005,10,19), _ Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15) -- Regards, Tom Ogilvy "Nigel" wrote in message ... 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" -- Cheers Nigel |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
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? -- Cheers Nigel "Tom Ogilvy" wrote in message ... try Range("A4:C4").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=" & DateSerial(2005,10,19), _ Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15) -- Regards, Tom Ogilvy "Nigel" wrote in message ... 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" -- Cheers Nigel |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
OK I have cracked it, I am using the following.......
Criteria1:="" & Format("13/10/2005", "mm/dd/yy") -- Cheers Nigel "Nigel" wrote in message ... 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? -- Cheers Nigel "Tom Ogilvy" wrote in message ... try Range("A4:C4").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=" & DateSerial(2005,10,19), _ Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15) -- Regards, Tom Ogilvy "Nigel" wrote in message ... 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" -- Cheers Nigel |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
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 --- Regards, Norman "Nigel" wrote in message ... 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? -- Cheers Nigel "Tom Ogilvy" wrote in message ... try Range("A4:C4").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=" & DateSerial(2005,10,19), _ Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15) -- Regards, Tom Ogilvy "Nigel" wrote in message ... 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" -- Cheers Nigel |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
Thanks, Norman. I will try this.
-- Cheers Nigel "Norman Jones" wrote in message ... 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 --- Regards, Norman "Nigel" wrote in message ... 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? -- Cheers Nigel "Tom Ogilvy" wrote in message ... try Range("A4:C4").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=" & DateSerial(2005,10,19), _ Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15) -- Regards, Tom Ogilvy "Nigel" wrote in message ... 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" -- Cheers Nigel |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
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) -- Regards, Tom Ogilvy "Norman Jones" wrote in message ... 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 --- Regards, Norman "Nigel" wrote in message ... 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? -- Cheers Nigel "Tom Ogilvy" wrote in message ... try Range("A4:C4").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=" & DateSerial(2005,10,19), _ Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15) -- Regards, Tom Ogilvy "Nigel" wrote in message ... 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" -- Cheers Nigel |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
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. --- Regards, Norman "Tom Ogilvy" wrote in message ... 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) -- Regards, Tom Ogilvy "Norman Jones" wrote in message ... 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 --- Regards, Norman "Nigel" wrote in message ... 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? -- Cheers Nigel "Tom Ogilvy" wrote in message ... try Range("A4:C4").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=" & DateSerial(2005,10,19), _ Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15) -- Regards, Tom Ogilvy "Nigel" wrote in message ... 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" -- Cheers Nigel |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter - Applying changes
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. -- Regards, Tom Ogilvy "Norman Jones" wrote in message ... 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. --- Regards, Norman "Tom Ogilvy" wrote in message ... 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) -- Regards, Tom Ogilvy "Norman Jones" wrote in message ... 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 --- Regards, Norman "Nigel" wrote in message ... 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? -- Cheers Nigel "Tom Ogilvy" wrote in message ... try Range("A4:C4").Select Selection.AutoFilter Selection.AutoFilter Field:=3, Criteria1:="=" & DateSerial(2005,10,19), _ Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15) -- Regards, Tom Ogilvy "Nigel" wrote in message ... 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" -- Cheers Nigel |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
| Counting the no. of entries after applying autofilter | Excel Discussion (Misc queries) | |||
| Applying an AutoFilter to a string | Excel Discussion (Misc queries) | |||
| Applying autofilter to protected sheet | Excel Discussion (Misc queries) | |||
| How To Count Rows Displayed After Applying AutoFilter | Excel Programming | |||
Linear Mode
