Solved: Reset a cell value on a certain day of the month

[Rob342]

I have an E mail sheet made up of the following

Col A : Persons Name
Col B : E Mail Address
Col C : Flag either set to "Y" or "N"
Approx 20 rows at present

On a certain day of the month ie the 30th, i need column C to change to "Y" so that an E Mail can be sent to the particular person.

I am struggling a bit with all the date functions on excel

Any help would be most appreciated

Rob

[Aussiebear]

Just to clear up a couple of points Rob, could you respond to the following:

With the possibility of either a Y or N flag in Column C, does this occur prior to the 30th?

If a column C cell already has a Y for another reason, does this still qualify for your requirement that all persons listed with a Y flag be sent the email?

What happens if a month has less than 30 days?

[Rob342]

Good Morning Aussiebear,
Thanks for your reply

I have attached a copy of the e mail sheet with dummy data for ref just to make it easier to understand, i think it might be better if A1 to A4 is always set to N as a default and A5 onwards are always set to Y as default, what do you think ? or do we set all to default to N to start?

A1 to A4 are always set to "N" unless it is the 30th of the month, or
we specifically manually override the cell with "Y".

A5 onwards are always set to "Y", unless we specifically overide the cell value.

As far as Febuary which has less than 30 days we could overide it for one month, if it is easier to code it that way.

Rob

[mdmackillop]

=IF(AND(MONTH(TODAY())=2,DAY(TODAY())=28),"Y",IF(DAY(TODAY())=30,"Y","N"))

[Rob342]

Thanks MD

Formula works ok but resets all values in col C to N, i could put in the formula just for the people that req the email for the 30th ?

Is this codeable via Vba ? possibly case select ?

Rob

[mdmackillop]

How are these people identified? Your workbook shows no way to discriminate.

[Rob342]

Hi MD

What if we put a indentifer in Col B as per attachment, make Col B manully set to either "W" for weekly or "M" for monthly?
Would that work ?

Rob

[mdmackillop]

Add another if to the formula to check for M values, but I don't know what you expect to see if W is entered. You need to think through all your options and be clear about expected results.

[Rob342]

Thanks MD
Decided to it another way and put a check box against each person, still have not found the code for vba dates.
If the check box is ticked to true then i need 2 routines on each chk box.

1 set to any day of the month
2 set to just the 30th of the month.


Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Range("C2").Value = "Y"
' add another routine for the dates ?
Else
Range("C2") = "N"
End If
End Sub

can you point me in the right direction
Rob

[mdmackillop]

Still not clear of your intentions. What is your logic?
I guess it is something like
If Col D is Y and Col B is W and today is Friday, send this one an email
If Col D is Y and Col B is M and today is last day of month, send this one an email.
or am I completely lost?

[Rob342]

Hi MD

You have hit the nail on the head.

If Col D is Y and Col B is W and today is Mon,Tues,Wed,Thurs & Friday, send this one an email
If Col D is Y and Col B is M and today is last day of month, send this one an email.

Rob

[mdmackillop]

Simplest with some code. This will write in an x if a mail is to be sent. It could be coded to send an email directly. See this post (http://www.vbaexpress.com/forum/showthread.php?t=36601)

Option Explicit

Sub Sendmail()
Dim Rng As Range, cel As Range
Set Rng = Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp))
For Each cel In Range("SendMail")
If UCase(cel) = "Y" Then
If cel.Offset(, -2) = "M" Then
Select Case Month(Date)
Case 2
If Day(Date) = 28 Then cel.Offset(, 2) = "x"
Case 4, 5, 9, 11
If Day(Date) = 31 Then cel.Offset(, 2) = "x"
Case Else
If Day(Date) = 30 Then cel.Offset(, 2) = "x"
End Select
Else
If Weekday(Date) <> 1 And Weekday(Date) <> 7 Then cel.Offset(, 2) = "x"
End If
End If
Next
End Sub

[Rob342]

Thanks MD

Getting error 1004 on the "For each" statement.

Rob

[Rob342]

Md

Solved the error, thanks for your time much appreciated

Rob