Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft Excel _ Automatically Change Date Vba

Posted by: wornout May 30 2019, 02:36 PM

I want to roll over the anniversary date

on my sheet1
In cell F20 I have a start date(Manually Entered)

In F22 I have some VBA that sets the anniversary date for a year from start date so far so good thumbup.gif

CODE
Sheet1.Range("F22").Value = DateAdd("d", 365, Range("F20"))


Now in a random cell(F218) I have this formula which works really well "=IF(DATEDIF(TODAY(),F22,"d")=1,DATE(YEAR(F22)+1,MONTH(F22),DAY(F22)),F22)"
what it does is if Today() is 1 day greater than anniversary date then update anniversary date by a year and it gives me the correct answer if it is not it keeps the original anniversary date

Now the question part
I would like to some how get that in vba so anniversary date updates with the new date automatically
So once the anniversary date has been reach the next day update the anniversary date by a year

Posted by: PaulBrand May 31 2019, 03:43 AM

Use YYYY instead of D to add a year (next year is a leap year)

CODE
Sheet1.Range("F22").Value = DateAdd("yyyy", 1, Range("F20"))

Iif(datediff("d",date(),Sheet1.Range("F22"))=1,Sheet1.Range("F22").Value = DateAdd("yyyy", 1, Range("F20")),Sheet1.Range("F22").Value)


Air code, untested