| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter Criteria not filtering when using NOW()
Hi,
I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
try doing
Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Now() -- Regards, Tom Ogilvy "bony_tony" wrote: Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "bony_tony" wrote in message ups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Still doesn't work.. Same problem..
On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Sub AAA()
ActiveSheet.Cells.Select Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Now() End Sub worked fine for me. -- Regards, Tom Ogilvy "bony_tony" wrote: Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Another attempt -
Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
I jumped in a bit too quick without fully testing, ignore that. There's a
bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
If they are dates this is working OK here for column A
And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Hi Ron,
Something strange going on for me, perhaps same as for the OP (I'm using XL2000). Maybe this is not correct Range("Note1").Column This is OK, I named B1 "Note1" and put dates in B2 down As I mentioned neither yours nor Tom's work for me. The code I posted previously does work for me but only if the dates in cells are formatted with default date format, for me that's International "dd-mm-yy" If I change numberformat in the date cells to say "dd-mm-yyyy" or "dd-mmm-yy" I cannot get anything to work correctly. ..NumberFormat returns "m/d/yy" ..NumberFormatLocal returns "dd-mm-yy" Referring to the code I posted previously, I need to use NumberFormat. The filter gives wrong results if I use NumberFormatLocal I put today's date in the middle of the dates. I recorded a macro to Filter Custom - 'is less than or equal to' and pointed to a cell with today's date The filter worked as expected, ie manually and recording. I replayed the macro - just like the OP - no rows in the filtered list at all ! Here's the recorded macro - Selection.AutoFilter Field:=2, Criteria1:="<=24-08-2007", Operator:=xlAnd Unless I'm missing something, the only way I see for the OP and me to get this working reliably would be to: - trap the cells' numberformat - clear the cells' numberformat - filter the date as a long or double number - reapply the original cells' date numberformat Regards, Peter T "Ron de Bruin" wrote in message ... If they are dates this is working OK here for column A And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Being in the US, I don't have the problem. However, in my test, I did change
the format in the key column to dd/mm/yyyy before running the code. In the past, I have had luck with some other users with this problem with regional versions (that use dates starting with day then month) by using the dateserial. So not sure what to tell the OP if he/she continues to fail and has accurately porrayed what he/she is trying to do. -- Regards, Tom Ogilvy "Peter T" wrote: I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
OK, this seems to work for me, irrespective of the numberformat in my cells
Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), "m/d/yy") Using XL2000 with default system date format "dd-mm-yy" Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Hi Ron, Something strange going on for me, perhaps same as for the OP (I'm using XL2000). Maybe this is not correct Range("Note1").Column This is OK, I named B1 "Note1" and put dates in B2 down As I mentioned neither yours nor Tom's work for me. The code I posted previously does work for me but only if the dates in cells are formatted with default date format, for me that's International "dd-mm-yy" If I change numberformat in the date cells to say "dd-mm-yyyy" or "dd-mmm-yy" I cannot get anything to work correctly. .NumberFormat returns "m/d/yy" .NumberFormatLocal returns "dd-mm-yy" Referring to the code I posted previously, I need to use NumberFormat. The filter gives wrong results if I use NumberFormatLocal I put today's date in the middle of the dates. I recorded a macro to Filter Custom - 'is less than or equal to' and pointed to a cell with today's date The filter worked as expected, ie manually and recording. I replayed the macro - just like the OP - no rows in the filtered list at all ! Here's the recorded macro - Selection.AutoFilter Field:=2, Criteria1:="<=24-08-2007", Operator:=xlAnd Unless I'm missing something, the only way I see for the OP and me to get this working reliably would be to: - trap the cells' numberformat - clear the cells' numberformat - filter the date as a long or double number - reapply the original cells' date numberformat Regards, Peter T "Ron de Bruin" wrote in message ... If they are dates this is working OK here for column A And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Can you send me your workbook Peter
Maybe i see the problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... Hi Ron, Something strange going on for me, perhaps same as for the OP (I'm using XL2000). Maybe this is not correct Range("Note1").Column This is OK, I named B1 "Note1" and put dates in B2 down As I mentioned neither yours nor Tom's work for me. The code I posted previously does work for me but only if the dates in cells are formatted with default date format, for me that's International "dd-mm-yy" If I change numberformat in the date cells to say "dd-mm-yyyy" or "dd-mmm-yy" I cannot get anything to work correctly. .NumberFormat returns "m/d/yy" .NumberFormatLocal returns "dd-mm-yy" Referring to the code I posted previously, I need to use NumberFormat. The filter gives wrong results if I use NumberFormatLocal I put today's date in the middle of the dates. I recorded a macro to Filter Custom - 'is less than or equal to' and pointed to a cell with today's date The filter worked as expected, ie manually and recording. I replayed the macro - just like the OP - no rows in the filtered list at all ! Here's the recorded macro - Selection.AutoFilter Field:=2, Criteria1:="<=24-08-2007", Operator:=xlAnd Unless I'm missing something, the only way I see for the OP and me to get this working reliably would be to: - trap the cells' numberformat - clear the cells' numberformat - filter the date as a long or double number - reapply the original cells' date numberformat Regards, Peter T "Ron de Bruin" wrote in message ... If they are dates this is working OK here for column A And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Got it Peter
Tomorrow I will test a few things on my Dutch system and post back I see the problem on my Dutch system and not on my US system. I remember now why I create the Date tab on the add-in <g See if the filter in my Add-in is working Correct for you http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Can you send me your workbook Peter Maybe i see the problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... Hi Ron, Something strange going on for me, perhaps same as for the OP (I'm using XL2000). Maybe this is not correct Range("Note1").Column This is OK, I named B1 "Note1" and put dates in B2 down As I mentioned neither yours nor Tom's work for me. The code I posted previously does work for me but only if the dates in cells are formatted with default date format, for me that's International "dd-mm-yy" If I change numberformat in the date cells to say "dd-mm-yyyy" or "dd-mmm-yy" I cannot get anything to work correctly. .NumberFormat returns "m/d/yy" .NumberFormatLocal returns "dd-mm-yy" Referring to the code I posted previously, I need to use NumberFormat. The filter gives wrong results if I use NumberFormatLocal I put today's date in the middle of the dates. I recorded a macro to Filter Custom - 'is less than or equal to' and pointed to a cell with today's date The filter worked as expected, ie manually and recording. I replayed the macro - just like the OP - no rows in the filtered list at all ! Here's the recorded macro - Selection.AutoFilter Field:=2, Criteria1:="<=24-08-2007", Operator:=xlAnd Unless I'm missing something, the only way I see for the OP and me to get this working reliably would be to: - trap the cells' numberformat - clear the cells' numberformat - filter the date as a long or double number - reapply the original cells' date numberformat Regards, Peter T "Ron de Bruin" wrote in message ... If they are dates this is working OK here for column A And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
OK, this is working on all systems as far as I know
When you use = it will filter on the text and not on the date value If you use <=, =, <, it will filter on the date value Because VBA is US we must always use the US date format Cells.AutoFilter 2, "<=08/20/2007" for dates <= 20 Aug 2007 If we enter for example DMY format Excel not know that it is a Date(number) Or Cells.AutoFilter 2, "<=" & CLng(Date) for dates <= today Bed time now for me, good night -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Got it Peter Tomorrow I will test a few things on my Dutch system and post back I see the problem on my Dutch system and not on my US system. I remember now why I create the Date tab on the add-in <g See if the filter in my Add-in is working Correct for you http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Can you send me your workbook Peter Maybe i see the problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... Hi Ron, Something strange going on for me, perhaps same as for the OP (I'm using XL2000). Maybe this is not correct Range("Note1").Column This is OK, I named B1 "Note1" and put dates in B2 down As I mentioned neither yours nor Tom's work for me. The code I posted previously does work for me but only if the dates in cells are formatted with default date format, for me that's International "dd-mm-yy" If I change numberformat in the date cells to say "dd-mm-yyyy" or "dd-mmm-yy" I cannot get anything to work correctly. .NumberFormat returns "m/d/yy" .NumberFormatLocal returns "dd-mm-yy" Referring to the code I posted previously, I need to use NumberFormat. The filter gives wrong results if I use NumberFormatLocal I put today's date in the middle of the dates. I recorded a macro to Filter Custom - 'is less than or equal to' and pointed to a cell with today's date The filter worked as expected, ie manually and recording. I replayed the macro - just like the OP - no rows in the filtered list at all ! Here's the recorded macro - Selection.AutoFilter Field:=2, Criteria1:="<=24-08-2007", Operator:=xlAnd Unless I'm missing something, the only way I see for the OP and me to get this working reliably would be to: - trap the cells' numberformat - clear the cells' numberformat - filter the date as a long or double number - reapply the original cells' date numberformat Regards, Peter T "Ron de Bruin" wrote in message ... If they are dates this is working OK here for column A And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Peter ask me how he can use =
The example below is always working if you want to filter column A for all dates of 15-Aug-2007 Range("A:A").AutoFilter Field:=1, Criteria1:="=08/15/2007", _ Operator:=xlAnd, Criteria2:="<=08/15/2007" ' Use always the US mm/dd/yyyy format Or if you enter the date in a worksheet cell (B1 in my example) you can do this Dim TheDate As Date Dim DateFormatString As String TheDate = Range("B1").Value DateFormatString = "mm/dd/yyyy" Range("A:A").AutoFilter Field:=1, Criteria1:="=" & Format(TheDate, DateFormatString), _ Operator:=xlAnd, Criteria2:="<=" & Format(TheDate, DateFormatString) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... OK, this is working on all systems as far as I know When you use = it will filter on the text and not on the date value If you use <=, =, <, it will filter on the date value Because VBA is US we must always use the US date format Cells.AutoFilter 2, "<=08/20/2007" for dates <= 20 Aug 2007 If we enter for example DMY format Excel not know that it is a Date(number) Or Cells.AutoFilter 2, "<=" & CLng(Date) for dates <= today Bed time now for me, good night -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Got it Peter Tomorrow I will test a few things on my Dutch system and post back I see the problem on my Dutch system and not on my US system. I remember now why I create the Date tab on the add-in <g See if the filter in my Add-in is working Correct for you http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Can you send me your workbook Peter Maybe i see the problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... Hi Ron, Something strange going on for me, perhaps same as for the OP (I'm using XL2000). Maybe this is not correct Range("Note1").Column This is OK, I named B1 "Note1" and put dates in B2 down As I mentioned neither yours nor Tom's work for me. The code I posted previously does work for me but only if the dates in cells are formatted with default date format, for me that's International "dd-mm-yy" If I change numberformat in the date cells to say "dd-mm-yyyy" or "dd-mmm-yy" I cannot get anything to work correctly. .NumberFormat returns "m/d/yy" .NumberFormatLocal returns "dd-mm-yy" Referring to the code I posted previously, I need to use NumberFormat. The filter gives wrong results if I use NumberFormatLocal I put today's date in the middle of the dates. I recorded a macro to Filter Custom - 'is less than or equal to' and pointed to a cell with today's date The filter worked as expected, ie manually and recording. I replayed the macro - just like the OP - no rows in the filtered list at all ! Here's the recorded macro - Selection.AutoFilter Field:=2, Criteria1:="<=24-08-2007", Operator:=xlAnd Unless I'm missing something, the only way I see for the OP and me to get this working reliably would be to: - trap the cells' numberformat - clear the cells' numberformat - filter the date as a long or double number - reapply the original cells' date numberformat Regards, Peter T "Ron de Bruin" wrote in message ... If they are dates this is working OK here for column A And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Or this one for = to find the exact match
Sub Test2() Dim TheDate As Date TheDate = Range("B1").Value Range("A:A").AutoFilter Field:=1, Criteria1:="=" & Month(TheDate) & "/" & Day(TheDate) & "/" & Year(TheDate) End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Peter ask me how he can use = The example below is always working if you want to filter column A for all dates of 15-Aug-2007 Range("A:A").AutoFilter Field:=1, Criteria1:="=08/15/2007", _ Operator:=xlAnd, Criteria2:="<=08/15/2007" ' Use always the US mm/dd/yyyy format Or if you enter the date in a worksheet cell (B1 in my example) you can do this Dim TheDate As Date Dim DateFormatString As String TheDate = Range("B1").Value DateFormatString = "mm/dd/yyyy" Range("A:A").AutoFilter Field:=1, Criteria1:="=" & Format(TheDate, DateFormatString), _ Operator:=xlAnd, Criteria2:="<=" & Format(TheDate, DateFormatString) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... OK, this is working on all systems as far as I know When you use = it will filter on the text and not on the date value If you use <=, =, <, it will filter on the date value Because VBA is US we must always use the US date format Cells.AutoFilter 2, "<=08/20/2007" for dates <= 20 Aug 2007 If we enter for example DMY format Excel not know that it is a Date(number) Or Cells.AutoFilter 2, "<=" & CLng(Date) for dates <= today Bed time now for me, good night -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Got it Peter Tomorrow I will test a few things on my Dutch system and post back I see the problem on my Dutch system and not on my US system. I remember now why I create the Date tab on the add-in <g See if the filter in my Add-in is working Correct for you http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Can you send me your workbook Peter Maybe i see the problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... Hi Ron, Something strange going on for me, perhaps same as for the OP (I'm using XL2000). Maybe this is not correct Range("Note1").Column This is OK, I named B1 "Note1" and put dates in B2 down As I mentioned neither yours nor Tom's work for me. The code I posted previously does work for me but only if the dates in cells are formatted with default date format, for me that's International "dd-mm-yy" If I change numberformat in the date cells to say "dd-mm-yyyy" or "dd-mmm-yy" I cannot get anything to work correctly. .NumberFormat returns "m/d/yy" .NumberFormatLocal returns "dd-mm-yy" Referring to the code I posted previously, I need to use NumberFormat. The filter gives wrong results if I use NumberFormatLocal I put today's date in the middle of the dates. I recorded a macro to Filter Custom - 'is less than or equal to' and pointed to a cell with today's date The filter worked as expected, ie manually and recording. I replayed the macro - just like the OP - no rows in the filtered list at all ! Here's the recorded macro - Selection.AutoFilter Field:=2, Criteria1:="<=24-08-2007", Operator:=xlAnd Unless I'm missing something, the only way I see for the OP and me to get this working reliably would be to: - trap the cells' numberformat - clear the cells' numberformat - filter the date as a long or double number - reapply the original cells' date numberformat Regards, Peter T "Ron de Bruin" wrote in message ... If they are dates this is working OK here for column A And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilter Criteria not filtering when using NOW()
Range("A:A").AutoFilter Field:=1, Criteria1:="=" & Month(TheDate) & "/" & Day(TheDate) & "/" & Year(TheDate)
Not working in every situation Use the other two examples I posted for = I stop this thread now because I am tired switching between my computers and talking to myself <g -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Or this one for = to find the exact match Sub Test2() Dim TheDate As Date TheDate = Range("B1").Value Range("A:A").AutoFilter Field:=1, Criteria1:="=" & Month(TheDate) & "/" & Day(TheDate) & "/" & Year(TheDate) End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Peter ask me how he can use = The example below is always working if you want to filter column A for all dates of 15-Aug-2007 Range("A:A").AutoFilter Field:=1, Criteria1:="=08/15/2007", _ Operator:=xlAnd, Criteria2:="<=08/15/2007" ' Use always the US mm/dd/yyyy format Or if you enter the date in a worksheet cell (B1 in my example) you can do this Dim TheDate As Date Dim DateFormatString As String TheDate = Range("B1").Value DateFormatString = "mm/dd/yyyy" Range("A:A").AutoFilter Field:=1, Criteria1:="=" & Format(TheDate, DateFormatString), _ Operator:=xlAnd, Criteria2:="<=" & Format(TheDate, DateFormatString) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... OK, this is working on all systems as far as I know When you use = it will filter on the text and not on the date value If you use <=, =, <, it will filter on the date value Because VBA is US we must always use the US date format Cells.AutoFilter 2, "<=08/20/2007" for dates <= 20 Aug 2007 If we enter for example DMY format Excel not know that it is a Date(number) Or Cells.AutoFilter 2, "<=" & CLng(Date) for dates <= today Bed time now for me, good night -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Got it Peter Tomorrow I will test a few things on my Dutch system and post back I see the problem on my Dutch system and not on my US system. I remember now why I create the Date tab on the add-in <g See if the filter in my Add-in is working Correct for you http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Can you send me your workbook Peter Maybe i see the problem -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... Hi Ron, Something strange going on for me, perhaps same as for the OP (I'm using XL2000). Maybe this is not correct Range("Note1").Column This is OK, I named B1 "Note1" and put dates in B2 down As I mentioned neither yours nor Tom's work for me. The code I posted previously does work for me but only if the dates in cells are formatted with default date format, for me that's International "dd-mm-yy" If I change numberformat in the date cells to say "dd-mm-yyyy" or "dd-mmm-yy" I cannot get anything to work correctly. .NumberFormat returns "m/d/yy" .NumberFormatLocal returns "dd-mm-yy" Referring to the code I posted previously, I need to use NumberFormat. The filter gives wrong results if I use NumberFormatLocal I put today's date in the middle of the dates. I recorded a macro to Filter Custom - 'is less than or equal to' and pointed to a cell with today's date The filter worked as expected, ie manually and recording. I replayed the macro - just like the OP - no rows in the filtered list at all ! Here's the recorded macro - Selection.AutoFilter Field:=2, Criteria1:="<=24-08-2007", Operator:=xlAnd Unless I'm missing something, the only way I see for the OP and me to get this working reliably would be to: - trap the cells' numberformat - clear the cells' numberformat - filter the date as a long or double number - reapply the original cells' date numberformat Regards, Peter T "Ron de Bruin" wrote in message ... If they are dates this is working OK here for column A And Tom's example will also work. Columns("A").AutoFilter Field:=1, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) Maybe this is not correct Range("Note1").Column -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Peter T" <peter_t@discussions wrote in message ... I jumped in a bit too quick without fully testing, ignore that. There's a bit more to it! Ron & Tom, neither of yours work for me either. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Another attempt - Sub test() Dim s As String ' asssumes all cells below the header have same date format s = Range("Note1").Offset(1).NumberFormat Selection.AutoFilter Field:=Range("Note1").Column, _ Criteria1:="<=" & Format(Now(), s) End Sub Regards, Peter T "bony_tony" wrote in message ups.com... Still doesn't work.. Same problem.. On 24 Aug, 15:08, "Ron de Bruin" wrote: Try DateSerial Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & DateSerial(Year(Date), Month(Date), Day(Date)) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "bony_tony" wrote in oglegroups.com... Hi, I have this line of code. Selection.AutoFilter Field:=Range("Note1").Column, Criteria1:="<=" & Format(Now(), "dd/mm/yyyy") I want to filter anything dated today or prior. It's not working - every line is being filtered out. When I drop down the autofilter button for the column, and go to 'Custom' I can see the correct date is in the dialog box, and the filter works when I press OK through it. Anyone know why this isn't working? Cheers Tony- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why is autofilter only filtering up to # of rows in prev column? | Excel Discussion (Misc queries) | |||
| Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet | Excel Programming | |||
| Autofilter Not Filtering Properly | Excel Discussion (Misc queries) | |||
| AutoFilter only filtering first 1000 | Excel Discussion (Misc queries) | |||
| Filtering out dates using autofilter | Excel Programming | |||
Linear Mode
