to find the birth day automatic in the list

ajaar · Mar 26, 2014

Hi,

I have Name and Date of birth listed in a sheet, if any of the date of birth( only DD-MMM) equal to today (only DD-MMM), i wanted to have come next cell Happy birthday.
If any of date of brithdate falling in Friday or saturday, it is to come in thursay itself.
I have started writing A VBA for that, dont know how to go ahead. any help appreciated.

file attached here with.

Regards
ajeer

iferror · Mar 26, 2014

IMHO VBA here is like shooting a fly with a cannon

Type this formula in D3 and copy down

Code:

=IF(TODAY()=DATE(YEAR(TODAY());MONTH(C3);DAY(C3)-IF(WEEKDAY(DATE(YEAR(NOW());MONTH(C3);DAY(C3));2)>5;WEEKDAY(DATE(YEAR(NOW());MONTH(C3);DAY(C3));2)-5;0));"HAPPY BIRTHDAY";"")

rumshar · Mar 26, 2014

@iferror
Good Formula

Smallman · Mar 26, 2014

Iferror - good job. I needed to put commas in the formula to get it to work at this end but work it did. What country are you in for XL to ask for semi colon's between criteria?

=IF(TODAY()=DATE(YEAR(TODAY()),MONTH(C3),DAY(C3)-IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(C3),DAY(C3)),2)>5,WEEKDAY(DATE(YEAR(NOW()),MONTH(C3),DAY(C3)),2)-5,0)),"HAPPY BIRTHDAY","")

Take care

Smallman

iferror · Mar 26, 2014

@Smallman: My PC is a European mixture of regional settings

Actually i think i've always used semicolon...even though i sort of have a blurry memory back from somewhere in my past, when (for a short time) i used to type in semicolons only to find out that XL was expecting commas (i think it was when i worked in Portugal...but i may be wrong)

Smallman · Mar 26, 2014

@iferror

Thanks for the update. I had seen it before and always wondered about its origin. Thanks again for the update

Smallman

ajaar · Mar 26, 2014

@iferror, thank you so much, it worked for me after changing ' ; ' to ' ,'.

I like your example of cannon, I would like to have cannon ready with me to shoot, tiger may come......

I am learning VBA, trying to do the Do while loop, but it is not working with if condition , appreciate your help on this, so i can handle similar situation.
Regards
Ajeer

iferror · Mar 26, 2014

Code:

Do While ActiveCell.Offset(0, 1) = dt
  ActiveCell.Offset(0, 2).Value = "happy birthday" & ActiveCell.Value
  ActiveCell.Offset(1, 0).Select
Loop

in plain english means:
Check the value in ActiveCell.Offset(0, 1)
is this value = to dt ?
if NO then exit the loop and go to the first line of code after Loop
if YES then type "happy birthday"+name and move one row down and do the check again

Add 2-3 rows at the TOP and set birthday as today, you'll see that the macro prints happy birthday for the 2-3 rows
Change all the dates to another day so that none is today. When you run the macro you have the feeling that nothing happens.

This is how i would do this

1) determine the range of used cells

Code:

Dim datesRng As Range
Set datesRng = Range("B3", Range("B" & Rows.Count).End(xlUp))

2)Loop the range, cell by cell

Code:

For Each cll In datesRng.Cells
  'now cll in the "Activecell"
  If cll.Offset(0, 1) = dt Then cll.Offset(0, 2).Value = "happy birthday " & cll.Value
Next

Smallman · Mar 26, 2014

ajaar

If you are looking to nail down some tidy code whenever you pause to start a loop ask yourself this question "Is it really necessary". There are faster ways to move through a range. Here is a method without a loop which gets exponentially faster the longer your list becomes. If you have 5000 names in your list you need to get vb to ask potentially 10,000 questions, does the cell equal today's date? (5000) and if so perform an action (potential 5000). While unlikely all people will have a birthday on the same day that is the potential for the loop. If there are no birthdays min is 5000 questions.

Better to ask one question of your dataset and perform one action (2). As your list grows those 2 questions will not increase in any way. As such I personally favour the following over looping constructs.

Code:

Sub MakeNew()
    Range("C2", Range("C" & Rows.Count).End(xlUp)).AutoFilter 1, Array(2, Date)
    Range("C3", Range("C" & Rows.Count).End(xlUp)).Offset(, 1) = "=""HappyBirthday ""&RC[-2]"
    [c2].AutoFilter
End Sub

Your dates were giving some grief so I rammed a result in the New tab of the attached.

Take care

Smallman

ajaar · Mar 26, 2014

@ iferror, thank you.i feel. it just like. you are just standing infront of me and giving me lesson. Thank you so much much appreciated.

@Smallman, thank you for taking me to the new thing, it is giving the result, it is my limitation that i didnt understand your code shown below.. which part of your code is checking date of birth = today , is it possible to explain to me or any link.. really appreciate your knowledge on this.
"=""HappyBirthday ""&RC[-2]"
[c2].AutoFilter

Regards
Ajeer

rumshar · Mar 26, 2014

@Smallman,
I have got few questions on this, would you mind to explain them?
1. what does Array(2, Date) mean?
2. I changed Cell(C6) of Sheet("New") to 3/26/2013 and macro didn't print formula next to this cell.Why?

attaching file herewith

With Regards
Rudra

Smallman · Mar 26, 2014

@ajaar

Thanks for the questions and interest in the topic. Basically we turn the autofilter on then where the criteria is met which is Date by the way (Date is the same as the today function in native Excel), then those items are shown in column C. So in Col D or 1 column offset from C we put happy birthday & then we put a formula which is two rows offset to the left of D Col B. In R1C1 language this is RC[-2]" . The R stand for ROW and the C stands for COLUMN. Do the following test. In F4 put the word Yes. Now in H5 put this code

Code:

Sub Goski()
[H5] = "=R[-1]C[-2]"
End Sub

You will see that the row is offset by -1 so row = 4 and the column is offset by -2 that means 2 columns less than H which is F. So return the value in F4 which is Yes.

To be totally honest you won't use the R1C1 notation as your coding improves. I had to use it in this case as I needed the relative position of the person's name and this was the method I chose because the position of the row the filter will reveal is unknown.

The final line [c2].AutoFilter just turns the filter off. Actually the same line turns the filter on again just to make it confusing. Strictly you should check if the filter is on a sheet before you produce code like this but I rarely do.

@rumshar

Thanks for the question too. The Array(2,Date) could just as easily be

Array(1, Date)
Array(0, Date) or even
Array(98, Date)

or probably what I should have gone with;

Array(Date)

I always have trouble filtering dates in vb and this seems to sort it nicely.

Your second question you are going to slap yourself over. We are in 2014 not 2013 and that is your answer.

Take care guys

Smallman

Abhijeet · Mar 26, 2014

Hi Use this Formula =IF(AND(DAY(B2)=DAY(C2),MONTH(B2)=MONTH(C2)),"Happy Birth Day","") Run The Macro Press ALT+8

SirJB7 · Mar 26, 2014

ajaar said:
If any of date of brithdate falling in Friday or saturday, it is to come in thursay itself.

@Abhijeet
Hi!
Your formula doesn't consider this.
Regards!

rumshar · Mar 27, 2014

@ Smallman,
Thanks for your explantion.But I am not gonna slap myself because I know what and why I am asking this.I am well aware that we are in 2014. Why I asked this is afterall we are trying to find whose birthday is falling on today's date and year becomes irrelevant here (Original Post clearly mentions this). I would like to print "Happy Birthday" to one who was born in 3/27/2001 also not only to one who was born today only.Sorry for my poor English, but I hope you got what I am trying to say.

With Regards
Rudra

ajaar · Mar 27, 2014

Hi friends,

It make me glad that my question caused for this much discussion in the Forum.
Thanks smallman, thanks all friends..
Rudra is right in his question, year should have irrelevant in the code.

Regards
Ajeer

Smallman · Mar 27, 2014

You are quite right. Any day on any year is your birthday. The way I would solve it would be to add the following;

Code:

Sub MakeNew()
    Dim dt As Integer
 
    dt = Month(Date) & Day(Date)
    Range("B3", Range("B" & Rows.Count).End(xlUp)).Offset(, -1) = "=MONTH(C3)&DAY(C3)"
    Range("A2", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, dt
    Range("C3", Range("C" & Rows.Count).End(xlUp)).Offset(, 1) = "=""HappyBirthday ""&RC[-2]"
    [c2].AutoFilter
    Columns(1).ClearContents.ClearContents
End Sub

Anyways I hope this assists Rudra. File attached.

Edit - I just realised the code does not consider the Thursday or Friday rule. The formula was more complete but it was a nice study in filtering by date.

Take care

Smallman

rumshar · Mar 27, 2014

Thanks Smallman,
This works fine for me.I must say you are a master of 'Array'.

With Regards
Rudra

Debraj · Mar 27, 2014

Here is another version...
Just a fusion of VBA & Formula base approach

Code:

Sub MakeNew()
    Range("D3:D" & Range("B" & Rows.Count).End(xlUp).Row) = _
        "=IF(TEXT(IF(WEEKDAY(C3,2)>5,C3-WEEKDAY(C3,2)-5,C3),""MMDD"")=TEXT(TODAY(),""MMDD""),""Happy Birthday "" &B3,"""")"
End Sub

Abhijeet · Mar 27, 2014

SirJB7 said:
@Abhijeet
Hi!
Your formula doesn't consider this.
Regards!

My formula Given Todays Birthday Given & blink that cell

SirJB7 · Mar 27, 2014

@Abhijeet
Hi!
Nobody denies that. What if you read my comment and what I quoted there? Is that asking a lot?
Regards!

to find the birth day automatic in the list

Member

Attachments

Member

Member

Excel Ninja

Member

Excel Ninja

Member

Member

Excel Ninja

Attachments

Member

Member

Attachments

Excel Ninja

Active Member

Attachments

Excel Rōnin

Member

Member

Excel Ninja

Attachments

Member

Excel Ninja

Active Member

Excel Rōnin