Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
Hi,
Excel 2007/2010 I'm trying to use the autofilter on a range using the actual date as a criteria. I have following code but it fails on the indicated code line. once i can autofilter for the actual date, then i need to select the apropriate data from columns B:D (visible rows) to send them to a recipient with mail. For sending mail, i'll visit the site of Ron De Bruin. I expect that RngToFilter is a valid range, so hoping i can address this imediately in the code as follow: RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails but it fails. Any idea what i'm doing wrong? Sub FilterByToday() ' Dim w As Worksheet Dim RngToFilter As Range Dim strToday As String 'get actual date strToday = Date Set w = Worksheets("Sheet1") With w 'reset the autofilter mode .AutoFilterMode = False 'set range to filter to the current region without the headers Set RngToFilter = .Cells.CurrentRegion RngToFilter.Offset(1, 0).Resize(RngToFilter.Rows.Count - 1, RngToFilter.Columns.Count).Select Set RngToFilter = Selection 'use this range to filter on the actual date RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails End With 'reset the autofilter mode w.AutoFilterMode = False 'clean up Set RngToFilter = Nothing Set w = Nothing End Sub here's my test data: Date article # serialnumber Description 27/07/2012 1 a aaaaaa 27/07/2012 2 b aaaaaa 27/07/2012 3 c ssssss 27/07/2012 4 d ddddd 27/07/2012 5 e cdccccc 27/07/2012 6 f xvbvfb 28/07/2012 1 a qdffg 28/07/2012 2 b svght 28/07/2012 3 c scdbf 28/07/2012 4 d ggjku 28/07/2012 5 e fgrrjk 28/07/2012 6 f xcvbfbn 28/07/2012 7 g adgvfhgj Any help welcome, Regards, Ludo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
Hi Ludo,
Am Sat, 28 Jul 2012 14:17:07 -0700 (PDT) schrieb Ludo: I'm trying to use the autofilter on a range using the actual date as a criteria. I have following code but it fails on the indicated code line. once i can autofilter for the actual date, then i need to select the apropriate data from columns B:D (visible rows) to send them to a recipient with mail. For sending mail, i'll visit the site of Ron De Bruin. I expect that RngToFilter is a valid range, so hoping i can address this imediately in the code as follow: RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails but it fails. Try: Sub FilterByToday() Dim wsh As Worksheet Dim RngToFilter As Range Set wsh = Worksheets("Sheet1") With wsh .AutoFilterMode = False Set RngToFilter = .Cells.CurrentRegion RngToFilter.AutoFilter Field:=1, Criteria1:=xlFilterToday, _ Operator:=xlFilterDynamic End With Set RngToFilter = Nothing Set wsh = Nothing End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
On Sat, 28 Jul 2012 14:17:07 -0700 (PDT), Ludo wrote:
Hi, Excel 2007/2010 I'm trying to use the autofilter on a range using the actual date as a criteria. I have following code but it fails on the indicated code line. once i can autofilter for the actual date, then i need to select the apropriate data from columns B:D (visible rows) to send them to a recipient with mail. For sending mail, i'll visit the site of Ron De Bruin. I expect that RngToFilter is a valid range, so hoping i can address this imediately in the code as follow: RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails but it fails. Any idea what i'm doing wrong? Sub FilterByToday() ' Dim w As Worksheet Dim RngToFilter As Range Dim strToday As String 'get actual date strToday = Date I'm pretty sure that the date needs to be in the format of m/d/yyyy. VBA is US-Centric So try strToday = format(date, "m/d/yyyy") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
Op zaterdag 28 juli 2012 23:37:25 UTC+2 schreef Claus Busch het volgende:
Hi Ludo, Am Sat, 28 Jul 2012 14:17:07 -0700 (PDT) schrieb Ludo: I'm trying to use the autofilter on a range using the actual date as a criteria. I have following code but it fails on the indicated code line. once i can autofilter for the actual date, then i need to select the apropriate data from columns B:D (visible rows) to send them to a recipient with mail. For sending mail, i'll visit the site of Ron De Bruin. I expect that RngToFilter is a valid range, so hoping i can address this imediately in the code as follow: RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, strToday)'<<<<<< error 1004 Method Autofilter from Class Range fails but it fails. Try: Sub FilterByToday() Dim wsh As Worksheet Dim RngToFilter As Range Set wsh = Worksheets("Sheet1") With wsh .AutoFilterMode = False Set RngToFilter = .Cells.CurrentRegion RngToFilter.AutoFilter Field:=1, Criteria1:=xlFilterToday, _ Operator:=xlFilterDynamic End With Set RngToFilter = Nothing Set wsh = Nothing End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Hi Claus, Thanks for the info. It works fine. Additional question: How did you know that you can use Criteria1:=xlFilterToday? I can't find this back in the Help for Excel. I guess that this knowledge belongs to the real experts :). Where can i find more 'expert' info abouth this topic (Autofilter)? Regards, Ludo |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
<SNIP
I'm pretty sure that the date needs to be in the format of m/d/yyyy. VBA is US-Centric So try strToday = format(date, "m/d/yyyy") Hi Ron, Tried it in my original code dut get still the same error. The sollution from Claus works pritty well. Anyhow thanks a lot for your remark. Working with date and time is sometimes a tricky issue. Regards, Ludo |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
<SNIP
Try: Sub FilterByToday() Dim wsh As Worksheet Dim RngToFilter As Range Set wsh = Worksheets("Sheet1") With wsh .AutoFilterMode = False Set RngToFilter = .Cells.CurrentRegion RngToFilter.AutoFilter Field:=1, Criteria1:=xlFilterToday, _ Operator:=xlFilterDynamic End With Set RngToFilter = Nothing Set wsh = Nothing End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Hi Claus, It works fine. Thanks for your help. Additional question: How did you know that you can use Criteria1:=xlFilterToday? Can't find this back in the Help for Excel :( I guess that this is 'expert' knowledge :) Where can i find more of this 'expert' info? Online, in books, ... Regards, Ludo |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
Hi Ludo,
Am Mon, 30 Jul 2012 05:08:50 -0700 (PDT) schrieb Ludo: Additional question: How did you know that you can use Criteria1:=xlFilterToday? Can't find this back in the Help for Excel :( I guess that this is 'expert' knowledge :) look in the VBA help for: XlDynamicFilterCriteria-Enumeration Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
Op maandag 30 juli 2012 14:19:16 UTC+2 schreef Claus Busch het volgende:
Hi Ludo, Am Mon, 30 Jul 2012 05:08:50 -0700 (PDT) schrieb Ludo: Additional question: How did you know that you can use Criteria1:=xlFilterToday? Can't find this back in the Help for Excel :( I guess that this is 'expert' knowledge :) look in the VBA help for: XlDynamicFilterCriteria-Enumeration Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Hi Claus, Thanks for the verry fast reply. I will check immediately. Regards, Ludo |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
Hi Ludo,
Am Mon, 30 Jul 2012 05:23:54 -0700 (PDT) schrieb Ludo: Thanks for the verry fast reply. I will check immediately. like Ron posted, your date must have the format MM/DD/YYYY. But I think you don't have a english version of excel. Formatting the date is not usefull therefore you have to create the string: strToday = Month(Date) & "/" & Day(Date) & "/" & Year(Date) and use it: RngToFilter.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:=strToday Or you write =Today() in G1 and then use it like this: ..AutoFilter Field:=1, Criteria1:="=" & .Range("G1").Value2 Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter problem
On Mon, 30 Jul 2012 05:00:42 -0700 (PDT), Ludo wrote:
<SNIP I'm pretty sure that the date needs to be in the format of m/d/yyyy. VBA is US-Centric So try strToday = format(date, "m/d/yyyy") Hi Ron, Tried it in my original code dut get still the same error. The sollution from Claus works pritty well. Anyhow thanks a lot for your remark. Working with date and time is sometimes a tricky issue. Regards, Ludo It is tricky. If you want to explore further, I'd try formatting strDate so that it matches the Short Date setting in your Windows Regional settings dialog (NOT the Excel Format). I suppose that might be d/m/yyyy or "short date" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilter Problem | Excel Discussion (Misc queries) | |||
autofilter problem | Excel Programming | |||
PROBLEM WITH AUTOFILTER | New Users to Excel | |||
autofilter problem | Excel Worksheet Functions | |||
AutoFilter Problem | Excel Programming |