Autofilter question

[rickblunt]

Greetings, I think that this may be any easy question and that I am just having a brain freeze or something. But I have a list of 400 people in column A and their birthdays is column B. So using the autofilter to sort out the dates is easy enough, but I would like to be able to sort out by week or month. For example 5 or 6 months from now, I pull up the spreadsheet and I want to see the birthdays for the upcoming week. I tried typing in "today()" in the custom filter box but that did not work so I tried this in a macro.

Sub WeekSearch()
    Selection.AutoFilter Field:=1, Criteria1:="Today()"
End Sub

Same result, (probably because there are only dates in the cells and not "Today()", right?)


Perhaps I am going about this in entirely the wrong way - I appreciate any input on how I should be doing this? Thanks, RB

 

[Simon Lloyd]

This is what you need:

Sub WeekSearch()
    Selection.AutoFilter Field:=1, Criteria1:=Date + 7
End Sub

 

[rickblunt]

nah, that doesn't return anything when I run it- the macro as you wrote it would need to know what "date" was anyway. Maybe I am mis-interpreting what you are writing? Thanks for trying though...

 

[Simon Lloyd]

It does return values when i run it on a column with dates!!!
Excel knows what DATE is because in VBA DATE is Today!!

 

[Simon Lloyd]

Maybe it doesn't return (filter) anything because there aren't any birthdays 7 days from now!

 

[rickblunt]

no, that would not be the case, first thing I would check - I have birthdays on the 5, 7, 8, 9, 10, 13,...

 

[Simon Lloyd]

Run this code:

Sub WeekSearch()
Dim MyCell As Range, Rng As Range, msg As String
Set Rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If CDate(MyCell.Value) > Date And CDate(MyCell.Value) < Date + 7 Then
msg = msg & "Bithday for " & MyCell.Offset(0, 1).Value & " on this date " & MyCell.Value & vbLf
End If
Next MyCell
MsgBox msg
End Sub

Assuming dates are in column B and names in column C

 

[Simon Lloyd]

no, that would not be the case, first thing I would check - I have birthdays on the 5, 7, 8, 9, 10, 13,...

As i said, for the previous solution, you dont have a birthday 7 days from now as that would be the 12th!

 

[rickblunt]

OK, that makes sense - but what I was trying to filter was all of the birthdays for the upcoming week....

 

[Simon Lloyd]

Did you try my last solution?