Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date criteria for adv. filter in code
I have a database in the following pattern:
DATE REMARK DEBIT CREDIT BANK CUST 01.01.09 xyxyxyx A X ----------------------------------------------------------- 10.04.09 jsstmjk B Y ---------------------------------------------------------- 10.07.09 sklsjhs A Y I want to extract the transactions of a certain customer (say X) between two dates and I want to do it in the code. My criteria range is like the following: Columns 5 6 7 Rows 1 DATE DATE CUST 2 =01.03.09 <=31.05.09 X Excel do not extract any records although there are several ones matching those criteria. Why? If I remove the dates then I get a correct extract for customer X but I cannot filter it between the given dates. Is there anything wrong with the punctuation in the cells for dates? P.S. I don't want to use the advance filter from the menu. It must be dealt with in the code. Regards, Lemi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date criteria for adv. filter in code
please show us your code. Your dates are not actually dates are they? you'll
need to change them to a standard form "Lemi" wrote in message ... I have a database in the following pattern: DATE REMARK DEBIT CREDIT BANK CUST 01.01.09 xyxyxyx A X ----------------------------------------------------------- 10.04.09 jsstmjk B Y ---------------------------------------------------------- 10.07.09 sklsjhs A Y I want to extract the transactions of a certain customer (say X) between two dates and I want to do it in the code. My criteria range is like the following: Columns 5 6 7 Rows 1 DATE DATE CUST 2 =01.03.09 <=31.05.09 X Excel do not extract any records although there are several ones matching those criteria. Why? If I remove the dates then I get a correct extract for customer X but I cannot filter it between the given dates. Is there anything wrong with the punctuation in the cells for dates? P.S. I don't want to use the advance filter from the menu. It must be dealt with in the code. Regards, Lemi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date criteria for adv. filter in code
Here is the code:
This is a sample code structure of which has been proposed by one of the Excel authorities in the group. There are three worksheets: one for the database, second for the criteria and the last one for the extract of data. The dates are chosen from the DatePicker controls in UserForm1 ----------------------------- Option Explicit Dim FirstDate As String Dim LastDate As String Sub ExtractUnique_1() UserForm1.Show FirstDate = "=" & UserForm1.DTPicker1.Value LastDate = "<=" & UserForm1.DTPicker2.Value With Sheets("Source Database") .Range(.Cells(1, 1), _ .Cells(.Rows.Count, 11) _ .End(xlUp)).Name = "Database" End With With Sheets("Criteria Data") .Range(.Cells(1, 6), .Cells(1, 8)) = Array("DATE", "DATE", "CUST2") .Range(.Cells(2, 6), .Cells(2, 8)) = Array(FirstDate, LastDate, "ETUR") .Range(.Cells(1, 6), .Cells(2, 8)).Name = "MyCriteria" End With With Sheets("Output Data") .Range(.Columns(1), .Columns(11)).Clear .Cells(1, 1).Name = "MyDestination" End With Range("Database").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("MyCriteria"), _ CopyToRange:=Range("MyDestination"), _ Unique:=False End Sub ------------------------------------- Regards, Lemi "Patrick Molloy" wrote in message ... please show us your code. Your dates are not actually dates are they? you'll need to change them to a standard form "Lemi" wrote in message ... I have a database in the following pattern: DATE REMARK DEBIT CREDIT BANK CUST 01.01.09 xyxyxyx A X ----------------------------------------------------------- 10.04.09 jsstmjk B Y ---------------------------------------------------------- 10.07.09 sklsjhs A Y I want to extract the transactions of a certain customer (say X) between two dates and I want to do it in the code. My criteria range is like the following: Columns 5 6 7 Rows 1 DATE DATE CUST 2 =01.03.09 <=31.05.09 X Excel do not extract any records although there are several ones matching those criteria. Why? If I remove the dates then I get a correct extract for customer X but I cannot filter it between the given dates. Is there anything wrong with the punctuation in the cells for dates? P.S. I don't want to use the advance filter from the menu. It must be dealt with in the code. Regards, Lemi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date criteria for adv. filter in code
Hi Lemi,
VBA does not recognize dates in d/m/y format for AdvancedFilter or AutoFilter Criteria. Date must be converted to either m/d/y format or use the literal month as in dd mmm yyyy format. Because textboxes are text by nature, firstly convert the text date to a date value and then format to either m/d/y or d mmm yyyy format. Example 1. FirstDate = ("=") & Format _ (DateValue(UserForm1 _ .DTPicker1.Value), "mm/dd/yy") LastDate = ("<=") & Format _ (DateValue(UserForm1 _ .DTPicker2.Value), "mm/dd/yy") Example 2. (Ossiemac's preferred format because self documenting) FirstDate = ("=") & Format _ (DateValue(UserForm1 _ .DTPicker1.Value), "dd mmm yy") LastDate = ("<=") & Format _ (DateValue(UserForm1 _ .DTPicker2.Value), "dd mmm yy") -- Regards, OssieMac "Lemi" wrote: Here is the code: This is a sample code structure of which has been proposed by one of the Excel authorities in the group. There are three worksheets: one for the database, second for the criteria and the last one for the extract of data. The dates are chosen from the DatePicker controls in UserForm1 ----------------------------- Option Explicit Dim FirstDate As String Dim LastDate As String Sub ExtractUnique_1() UserForm1.Show FirstDate = "=" & UserForm1.DTPicker1.Value LastDate = "<=" & UserForm1.DTPicker2.Value With Sheets("Source Database") .Range(.Cells(1, 1), _ .Cells(.Rows.Count, 11) _ .End(xlUp)).Name = "Database" End With With Sheets("Criteria Data") .Range(.Cells(1, 6), .Cells(1, 8)) = Array("DATE", "DATE", "CUST2") .Range(.Cells(2, 6), .Cells(2, 8)) = Array(FirstDate, LastDate, "ETUR") .Range(.Cells(1, 6), .Cells(2, 8)).Name = "MyCriteria" End With With Sheets("Output Data") .Range(.Columns(1), .Columns(11)).Clear .Cells(1, 1).Name = "MyDestination" End With Range("Database").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Range("MyCriteria"), _ CopyToRange:=Range("MyDestination"), _ Unique:=False End Sub ------------------------------------- Regards, Lemi "Patrick Molloy" wrote in message ... please show us your code. Your dates are not actually dates are they? you'll need to change them to a standard form "Lemi" wrote in message ... I have a database in the following pattern: DATE REMARK DEBIT CREDIT BANK CUST 01.01.09 xyxyxyx A X ----------------------------------------------------------- 10.04.09 jsstmjk B Y ---------------------------------------------------------- 10.07.09 sklsjhs A Y I want to extract the transactions of a certain customer (say X) between two dates and I want to do it in the code. My criteria range is like the following: Columns 5 6 7 Rows 1 DATE DATE CUST 2 =01.03.09 <=31.05.09 X Excel do not extract any records although there are several ones matching those criteria. Why? If I remove the dates then I get a correct extract for customer X but I cannot filter it between the given dates. Is there anything wrong with the punctuation in the cells for dates? P.S. I don't want to use the advance filter from the menu. It must be dealt with in the code. Regards, Lemi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro filter based on date criteria | Excel Discussion (Misc queries) | |||
Displaying date in filter criteria | Excel Discussion (Misc queries) | |||
VBA Code for Custom Date Filter | Excel Programming | |||
Help with using filter criteria with date | Excel Worksheet Functions | |||
Help with passing date value into Auto filter criteria | Excel Programming |