I want to filter some data by, among other things, whether the date in one of
the columns is between the date the macro runs and Saturday of that week. The
macro will not always be run on the same day of the week. I searched high and
low on this board, but found nothing like that. I think what may work is
using a dialog box, where the person running the macro would be asked how
many days until Saturday. I would insert a column into the data that
calculated the number of days between the current date and Saturday, and any
rows where the number generated by the dialog box was less than or equal to
the number showing in my calculated column would be filtered out.

Does that sound like a reasonable solution? Any better ones? I don't know a
thing about dialog boxes, but if it will work, I guess I'll do some self
educating....

If you doing an autofilter, then you can put in a custom criteria that the
data less than or eqaul to 1 date (next Saturday) and greater than or equal
to another date(today's date).

if you are in the US or Canada, this should be no problem. turn on the
macro recorder and apply the dates manually to the filter. Now turn the
macro recorder off and look at the recorded code. You can replace the hard
coded dates recorded with your calculated dates (using the Weekday function)

--
Regards,
Tom Ogilvy


"jmdaniel" wrote:

I want to filter some data by, among other things, whether the date in one of
the columns is between the date the macro runs and Saturday of that week. The
macro will not always be run on the same day of the week. I searched high and
low on this board, but found nothing like that. I think what may work is
using a dialog box, where the person running the macro would be asked how
many days until Saturday. I would insert a column into the data that
calculated the number of days between the current date and Saturday, and any
rows where the number generated by the dialog box was less than or equal to
the number showing in my calculated column would be filtered out.

Does that sound like a reasonable solution? Any better ones? I don't know a
thing about dialog boxes, but if it will work, I guess I'll do some self
educating....

Add a helper column with a formula of say

=AND(TODAY()<=C2,C2<=TODAY()+7-WEEKDAY(TODAY()))

and then filter that column on a value of TRUE

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jmdaniel" wrote in message
...
I want to filter some data by, among other things, whether the date in one
of
the columns is between the date the macro runs and Saturday of that week.
The
macro will not always be run on the same day of the week. I searched high
and
low on this board, but found nothing like that. I think what may work is
using a dialog box, where the person running the macro would be asked how
many days until Saturday. I would insert a column into the data that
calculated the number of days between the current date and Saturday, and
any
rows where the number generated by the dialog box was less than or equal
to
the number showing in my calculated column would be filtered out.

Does that sound like a reasonable solution? Any better ones? I don't know
a
thing about dialog boxes, but if it will work, I guess I'll do some self
educating....

Unless I misunderstand your answer, I'm not sure how this will work when the
worker is running the macro on different days of the week. For instance, if
the macro is hard-coded using Monday as the date the macro is being run, and
the macro is actually run on Wednesday, woudln't the results of the macro be
incorrect?

"Tom Ogilvy" wrote:

If you doing an autofilter, then you can put in a custom criteria that the
data less than or eqaul to 1 date (next Saturday) and greater than or equal
to another date(today's date).

if you are in the US or Canada, this should be no problem. turn on the
macro recorder and apply the dates manually to the filter. Now turn the
macro recorder off and look at the recorded code. You can replace the hard
coded dates recorded with your calculated dates (using the Weekday function)

--
Regards,
Tom Ogilvy


"jmdaniel" wrote:

I want to filter some data by, among other things, whether the date in one of
the columns is between the date the macro runs and Saturday of that week. The
macro will not always be run on the same day of the week. I searched high and
low on this board, but found nothing like that. I think what may work is
using a dialog box, where the person running the macro would be asked how
many days until Saturday. I would insert a column into the data that
calculated the number of days between the current date and Saturday, and any
rows where the number generated by the dialog box was less than or equal to
the number showing in my calculated column would be filtered out.

Does that sound like a reasonable solution? Any better ones? I don't know a
thing about dialog boxes, but if it will work, I guess I'll do some self
educating....

Well sure, but why would you hard code the date when you can use the Date
function to get today's date. That is why you use macros, - to make things
dynamic - or at least one reason.

On my test sheet, I had my dates in column 2 with headers in Row1 and
staring in A1:

Sub Macro1()
ActiveSheet.AutoFilterMode = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
s = rng(1).Offset(1, 1).NumberFormat
sStart = Format(Date, s)
sSaturday = Format(Date + 7 - Weekday(Date, 1), s)
rng.AutoFilter Field:=2, Criteria1:="=" & sStart, _
Operator:=xlAnd, _
Criteria2:="<=" & sSaturday

End Sub



--
Regards,
Tom Ogilvy


"jmdaniel" wrote:

Unless I misunderstand your answer, I'm not sure how this will work when the
worker is running the macro on different days of the week. For instance, if
the macro is hard-coded using Monday as the date the macro is being run, and
the macro is actually run on Wednesday, woudln't the results of the macro be
incorrect?

"Tom Ogilvy" wrote:

If you doing an autofilter, then you can put in a custom criteria that the
data less than or eqaul to 1 date (next Saturday) and greater than or equal
to another date(today's date).

if you are in the US or Canada, this should be no problem. turn on the
macro recorder and apply the dates manually to the filter. Now turn the
macro recorder off and look at the recorded code. You can replace the hard
coded dates recorded with your calculated dates (using the Weekday function)

--
Regards,
Tom Ogilvy


"jmdaniel" wrote:

I want to filter some data by, among other things, whether the date in one of
the columns is between the date the macro runs and Saturday of that week. The
macro will not always be run on the same day of the week. I searched high and
low on this board, but found nothing like that. I think what may work is
using a dialog box, where the person running the macro would be asked how
many days until Saturday. I would insert a column into the data that
calculated the number of days between the current date and Saturday, and any
rows where the number generated by the dialog box was less than or equal to
the number showing in my calculated column would be filtered out.

Does that sound like a reasonable solution? Any better ones? I don't know a
thing about dialog boxes, but if it will work, I guess I'll do some self
educating....

Very good! My data is actually in Column I, so just a couple of mods, and it
works like a charm:

Sub Macro1()
ActiveSheet.AutoFilterMode = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
s = rng(1).Offset(1, 9).NumberFormat
sStart = Format(Date, s)
sSaturday = Format(Date + 7 - Weekday(Date, 1), s)
rng.AutoFilter Field:=9, Criteria1:="=" & sStart, _
Operator:=xlAnd, _
Criteria2:="<=" & sSaturday

End Sub


Thank you very much!

"Tom Ogilvy" wrote:

Well sure, but why would you hard code the date when you can use the Date
function to get today's date. That is why you use macros, - to make things
dynamic - or at least one reason.

On my test sheet, I had my dates in column 2 with headers in Row1 and
staring in A1:

Sub Macro1()
ActiveSheet.AutoFilterMode = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
s = rng(1).Offset(1, 1).NumberFormat
sStart = Format(Date, s)
sSaturday = Format(Date + 7 - Weekday(Date, 1), s)
rng.AutoFilter Field:=2, Criteria1:="=" & sStart, _
Operator:=xlAnd, _
Criteria2:="<=" & sSaturday

End Sub



--
Regards,
Tom Ogilvy


"jmdaniel" wrote:

Unless I misunderstand your answer, I'm not sure how this will work when the
worker is running the macro on different days of the week. For instance, if
the macro is hard-coded using Monday as the date the macro is being run, and
the macro is actually run on Wednesday, woudln't the results of the macro be
incorrect?

"Tom Ogilvy" wrote:

If you doing an autofilter, then you can put in a custom criteria that the
data less than or eqaul to 1 date (next Saturday) and greater than or equal
to another date(today's date).

if you are in the US or Canada, this should be no problem. turn on the
macro recorder and apply the dates manually to the filter. Now turn the
macro recorder off and look at the recorded code. You can replace the hard
coded dates recorded with your calculated dates (using the Weekday function)

--
Regards,
Tom Ogilvy


"jmdaniel" wrote:

I want to filter some data by, among other things, whether the date in one of
the columns is between the date the macro runs and Saturday of that week. The
macro will not always be run on the same day of the week. I searched high and
low on this board, but found nothing like that. I think what may work is
using a dialog box, where the person running the macro would be asked how
many days until Saturday. I would insert a column into the data that
calculated the number of days between the current date and Saturday, and any
rows where the number generated by the dialog box was less than or equal to
the number showing in my calculated column would be filtered out.

Does that sound like a reasonable solution? Any better ones? I don't know a
thing about dialog boxes, but if it will work, I guess I'll do some self
educating....

Bob,

This worked as well. I went with Tom's, so I didn't have to throw some steps
in to copy that formula down, but this was a big help as well.


Thanks,
Jeff

"Bob Phillips" wrote:

Add a helper column with a formula of say

=AND(TODAY()<=C2,C2<=TODAY()+7-WEEKDAY(TODAY()))

and then filter that column on a value of TRUE

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jmdaniel" wrote in message
...
I want to filter some data by, among other things, whether the date in one
of
the columns is between the date the macro runs and Saturday of that week.
The
macro will not always be run on the same day of the week. I searched high
and
low on this board, but found nothing like that. I think what may work is
using a dialog box, where the person running the macro would be asked how
many days until Saturday. I would insert a column into the data that
calculated the number of days between the current date and Saturday, and
any
rows where the number generated by the dialog box was less than or equal
to
the number showing in my calculated column would be filtered out.

Does that sound like a reasonable solution? Any better ones? I don't know
a
thing about dialog boxes, but if it will work, I guess I'll do some self
educating....

Tom,

Can you help with one more thing? If I wanted to filter another file, same
column, but this time just for rows that had today's date, how would I modify
the macro? I tried to pull a few of the "Saturday" rows out, but that simply
filtered all the rows...



"Tom Ogilvy" wrote:

Well sure, but why would you hard code the date when you can use the Date
function to get today's date. That is why you use macros, - to make things
dynamic - or at least one reason.

On my test sheet, I had my dates in column 2 with headers in Row1 and
staring in A1:

Sub Macro1()
ActiveSheet.AutoFilterMode = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
s = rng(1).Offset(1, 1).NumberFormat
sStart = Format(Date, s)
sSaturday = Format(Date + 7 - Weekday(Date, 1), s)
rng.AutoFilter Field:=2, Criteria1:="=" & sStart, _
Operator:=xlAnd, _
Criteria2:="<=" & sSaturday

End Sub



--
Regards,
Tom Ogilvy


"jmdaniel" wrote:

Unless I misunderstand your answer, I'm not sure how this will work when the
worker is running the macro on different days of the week. For instance, if
the macro is hard-coded using Monday as the date the macro is being run, and
the macro is actually run on Wednesday, woudln't the results of the macro be
incorrect?

"Tom Ogilvy" wrote:

If you doing an autofilter, then you can put in a custom criteria that the
data less than or eqaul to 1 date (next Saturday) and greater than or equal
to another date(today's date).

if you are in the US or Canada, this should be no problem. turn on the
macro recorder and apply the dates manually to the filter. Now turn the
macro recorder off and look at the recorded code. You can replace the hard
coded dates recorded with your calculated dates (using the Weekday function)

--
Regards,
Tom Ogilvy


"jmdaniel" wrote:

I want to filter some data by, among other things, whether the date in one of
the columns is between the date the macro runs and Saturday of that week. The
macro will not always be run on the same day of the week. I searched high and
low on this board, but found nothing like that. I think what may work is
using a dialog box, where the person running the macro would be asked how
many days until Saturday. I would insert a column into the data that
calculated the number of days between the current date and Saturday, and any
rows where the number generated by the dialog box was less than or equal to
the number showing in my calculated column would be filtered out.

Does that sound like a reasonable solution? Any better ones? I don't know a
thing about dialog boxes, but if it will work, I guess I'll do some self
educating....