Setting a criteria in an AdvancedFilter macro

[Wendell A. Clark]

I recorded a macro that gave me this code

Sheets("Sheet1").Range("D5:X306").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L5:L306"),
CopyToRange:=Range("D5") _
, Unique:=False

the criteria I would like to set is based on colomn 9 (which equates to
L5:L306 and it will probably vary with each run) of the affected range it
is a date field, I would like to restrict the the dates to a set a range
such >=01/01/2006 and <= 06/30/2006.

How do I set up the criteria range?

What would I need to do to make this work?

Any help would be greatly appreciated
Thanks

--
Wendell A. Clark, BS
-------------------------------------

CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may
contain confidential and privileged information for the use of the
designated recipients named above. If you are not the intended recipient,
please notify us by reply e-mail. You are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is prohibited.
If you have received this communication in error, please destroy all copies
of this communication and any attachments. Contact the sender if it
continues.

 

[Tom Ogilvy]

http://www.contextures.com/tiptech.html

look at the first entries for Advanced Filter.

 

[Wendell A. Clark]

Tom,
Thanks for the link I will check it out

I came up with the following code, but it doesn't seem to work correctly
(neither does it error out) -- maybe a date format issue -- perhaps the link
will give me some light -- again thanks..

Dim startDate As Date
Dim endDate As Date
startDate = Sheets("Report").Range("D3")
endDate = Sheets("Report").Range("F3")
Dim strCriteria As String
strCriteria = "Field:=9, Criteria1:="">=" & startDate & """,
Operator:=xlAnd, Field:=9, Criteria2:=""<=" & endDate & """"
Sheets("RAW").Range("D2") = strCriteria
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Sheets("Haven").Select

Sheets("Raw").Range("D5:X" & LastRow).AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("RAW").Range("D2"), CopyToRange:=Range("D5"),
_
Unique:=False


--
Wendell A. Clark, BS
-------------------------------------

CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may
contain confidential and privileged information for the use of the
designated recipients named above. If you are not the intended recipient,
please notify us by reply e-mail. You are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is prohibited.
If you have received this communication in error, please destroy all copies
of this communication and any attachments. Contact the sender if it
continues.

 

[Wendell A. Clark]

Tom,
The link did provide some direction
Also I learned that you cannot use the construct >=01/01/2006 or <=
6/30/2006 as criteria cells (no data is rturned)

They need to be listed

colTitle colTitle

01/01/2006 <06/30/2006

01/01/2006 06/30/2006

then give the full 6 cells as the criteria range -- viola then it worked

Again thanks for your direction


--
Wendell A. Clark, BS
-------------------------------------

CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may
contain confidential and privileged information for the use of the
designated recipients named above. If you are not the intended recipient,
please notify us by reply e-mail. You are hereby notified that you have
received this communication in error and that any review, disclosure,
dissemination, distribution or copying of it or its contents is prohibited.
If you have received this communication in error, please destroy all copies
of this communication and any attachments. Contact the sender if it
continues.