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" <(E-Mail Removed)> wrote in message
> news:36C82907-56DC-4BA7-A16E-(E-Mail Removed)...
> > please show us your code. Your dates are not actually dates are they?
> > you'll need to change them to a standard form
> >
> > "Lemi" <(E-Mail Removed)> wrote in message
> > news:#(E-Mail Removed)...
> >> 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
> >>
>
>
>
|
Similar Threads
