I have a column of data (C) that has many cells that contain dates. I am trying to create a macro that checks to see if each cell contains a date and if it does then advance the date on month. I have the code to advance the date one month from here and it works fine but I am not sure how to replace the range with a dynamic range that evaluates all cells in column C. If possible I would also like to eliminate the need for a loop. Here is what I have so far.
Sub IncreaseMonth() Dim dDate As Date Dim NumberofTasks As Integer NumberofTasks = ThisWorkbook.Worksheets("Dashboard").Range("Number_of_Tasks") Dim x As Integer For x = 1 To NumberofTasks dDate = Range("C30").Value Range("C30").Value = _ DateSerial(Year(dDate), _ Month(dDate) + 1, Day(dDate)) Next x End Sub 2 Answers
Try something like the code below (I use DateAdd function to add 1 Month to the current date value)
Sub IncreaseMonth() Dim dDate As Date Dim NumberofTasks As Long Dim x As Long With Worksheets("Dashboard") ' I suspect you want to get the last row with data in Column C NumberofTasks = .Cells(.Rows.Count, "C").End(xlUp).Row For x = 1 To NumberofTasks If IsDate(.Range("C" & x).Value) Then '<-- check if current cell at Column C is Date .Range("C" & x).Value = DateAdd("m", 1, .Range("C" & x).Value) '<-- add 1 Month to current date in Column c, use DateAdd function End If Next x End With End Sub This snippet should put you on the right track. I'm making a couple of assumptions here. The first is that you have a named range called "Number_of_Tasks" on which you wish to operate. Second is that all values in this range are a valid date. If values could be an invalid date (like a blank) you should check for this before setting the value.
You will also wish to ensure that the month does not become invalid. Incrementing the month past December will not be a valid date.
Sub IncreaseMonth() Dim tempCell As Range For Each tempCell In ThisWorkbook.Worksheets("Dashboard").Range("Number_of_Tasks") tempCell.Value = DateSerial(Year(tempCell.value), Month(tempCell.value) + 1, Day(tempCell.value)) Next tempCell 
