#16
|
|||
|
|||
Awesome. I had seen that but forgot that I had added new rows (my first blank cell in the target column is G12, so I changed to that in the code). Thank you so much again. This is so helpful! |
#17
|
|||
|
|||
So my colleague has informed me of a small issue. Many case managers will be inputting intake dates from several years ago, so the current code will only add one year rather than bring the date to the current year.
Is it possible to make the year update to the current year rather than adding 1 (still keeping the month and day the same, and still only making the change if 12 months have passed)? |
#18
|
|||
|
|||
Try changing
Code:
cel = DateAdd("yyyy", 1, cel) Code:
cel = DateSerial(Year(Date), Month(cel.Value), Day(cel.Value)) |
#19
|
|||
|
|||
Gives me a runtime error (although did seem to work). Not sure how to clear off the runtime error. My columns and rows have shifted again (target column is now E and first blank cell is E4).
|
#20
|
|||
|
|||
Are you trying to use that code for the sheet pictured in post #16, where the dates are in column G ?
|
#21
|
|||
|
|||
Quote:
Code:
Sub UpdateIntakeDate() Dim rng As Range, cel As Range, lr As Long With Sheets("Client Tracker") lr = .Cells(.Rows.Count, "E").End(xlUp).Row If lr < 12 Then Exit Sub Set rng = .Range("E6:E" & lr) For Each cel In rng If cel <> "" Then If Date >= DateAdd("yyyy", 1, cel) Then cel = DateSerial(Year(Date), Month(cel.Value), Day(cel.Value)) End If End If Next cel End With End Sub |
#22
|
|||
|
|||
Here's the code, commented line by line for better understanding.
Code:
Sub UpdateIntakeDate() Dim rng As Range 'the overall range to work on Dim cel As Range 'the one cell to work on at a time Dim lr As Long 'the last row with data in the column With Sheets("Client Tracker") 'determine last used row in the column lr = .Cells(.Rows.Count, "E").End(xlUp).Row 'if lr is less than first row that would have dates don't go any further If lr < 6 Then Exit Sub '<<<<<<<<<<<<< 'set the range to work on Set rng = .Range("E6:E" & lr) 'work on the cells in the range one at a time For Each cel In rng 'if the cell IS NOT blank If cel <> "" Then 'check if today is greater than or equal to the date in the cell plus one year If Date >= DateAdd("yyyy", 1, cel) Then 'if it is then give it the new date cel = DateSerial(Year(Date), Month(cel.Value), Day(cel.Value)) End If End If 'move on to the next cel by going back to the start of For Each Next cel End With End Sub |
#23
|
|||
|
|||
That's very helpful NoSparks, thank you. This seems to work well, and I appreciate the comments (def helped me understand what was going on a bit better). Thanks for all your assistance throughout this thread!
|
Tags |
automatically, update, year |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto update excel graph range, ignore data when date reads 00-Month-00 | SavGDK | Excel | 2 | 06-24-2016 08:05 AM |
Auto populating dates on timesheet for week from one inputted date | ohi | Word VBA | 3 | 12-04-2014 12:11 PM |
Date/Time Formula for Entry level XL user | talon1driver | Excel | 2 | 09-18-2014 02:32 PM |
Mail merge with filter using data inputted by user | Tornado70 | Mail Merge | 6 | 04-09-2013 07:16 AM |
How to update automatically the “file name” as we do for the “Date”? | Jamal NUMAN | Word | 2 | 01-06-2011 02:43 PM |