Autofilter question

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
468
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.

Code:
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
This is what you need:
Code:
Sub WeekSearch()
    Selection.AutoFilter Field:=1, Criteria1:=Date + 7
End Sub
 

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
468
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

Well-known Member
Joined
Sep 10, 2006
Messages
756
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

Well-known Member
Joined
Sep 10, 2006
Messages
756

ADVERTISEMENT

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

rickblunt

Active Member
Joined
Feb 18, 2008
Messages
468
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

Well-known Member
Joined
Sep 10, 2006
Messages
756

ADVERTISEMENT

Run this code:
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

Well-known Member
Joined
Sep 10, 2006
Messages
756
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

Active Member
Joined
Feb 18, 2008
Messages
468
OK, that makes sense - but what I was trying to filter was all of the birthdays for the upcoming week....
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,325
Messages
5,510,594
Members
408,806
Latest member
Hunlight

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top