Sorting a data list with a variable

[spovolny]

Hi -
I've got a "list" (an Excel list, with dropdown arrows to sort by...)
which contains dates among other information. What I need to do is
write a quick macro for the user to be able to sort one of the columns
by a week. So for instance, if the column contained these dates:

START DATE - Column A
7/16/2007
7/16/2007
7/17/2007
7/18/2007
7/19/2007
7/26/2007
7/31/2007
8/01/2007

and I clicked on the dropdown arrow to sort by the date - and selected
"custom", I would enter = today's date (7/16/2007) from START DATE and
I would enter AND is less than 7/23/2007 to select all of the dates
for one week time period.

This works fine - but how do I replicate it with variables for today's
date, and today's date + 7 (to capture the week) in VBA code?

This code did not work - any ideas, or need me to elaborate? THANKS!!

Dim dteToday As Date
dteToday = Now()

Selection.AutoFilter Field:=2, Criteria1:="=dteToday",
Operator:=xlAnd, _
Criteria2:="<dteToday + 7"

 

[Dave Peterson]

First, you're not really sorting your data--you're just filtering it.

You could try:

Dim dteToday As Date
dteToday = Date '

Selection.AutoFilter Field:=2, Criteria1:="=" & dteToday, Operator:=xlAnd, _
Criteria2:="<" & dteToday + 7

If that fails and it may...Dates and VBA and filtering don't always work
nicely...try:

Selection.AutoFilter Field:=2, Criteria1:="=" & clng(dteToday), Operator:=xlAnd,
_
Criteria2:="<" & clng(dteToday + 7)

If that fails and it may....

Selection.AutoFilter Field:=2, Criteria1:="=" & format(dteToday, "mm/dd/yyyy"),
_
Operator:=xlAnd, Criteria2:="<" & format(dteToday + 7, "mm/dd/yyyy")

Use the same format as you used in the worksheet.

I'm betting that one of those will work.

=========
But I'm kind of confused about the criteria.

You're filtering on =today'sDate and <today'sDate+7

If you were using: criteria1:=">=" & ....

It would make more sense to me. But today's date will always be less than
today's date + 7.