使用 VBA 检查单元格是否包含日期，如果是，则将日期提前一个月
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41704039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using VBA to check if a cell contains a date and if so advancing the date one month
提问by TonyP
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 http://excel.tips.net/T002180_Automatically_Advancing_by_a_Month.htmland 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.
我有一列数据 (C)，其中包含许多包含日期的单元格。我正在尝试创建一个宏来检查每个单元格是否包含日期，如果它包含则将日期提前。我有将日期从这里提前一个月的代码http://excel.tips.net/T002180_Automatically_Advancing_by_a_Month.html并且它工作正常，但我不确定如何用评估 C 列中所有单元格的动态范围替换该范围. 如果可能的话，我还想消除循环的需要。这是我到目前为止所拥有的。
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
回答by Shai Rado
Try something like the code below (I use DateAddfunction to add 1 Month to the current date value)
尝试类似下面的代码（我使用DateAdd函数将 1 Month 添加到当前日期值）
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
回答by Taelsin
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.
这个片段应该让你走上正确的轨道。我在这里做一些假设。第一个是您有一个名为“Number_of_Tasks”的命名范围，您希望对其进行操作。其次是此范围内的所有值都是有效日期。如果值可能是无效日期（如空白），您应该在设置值之前检查这一点。
You will also wish to ensure that the month does not become invalid. Incrementing the month past December will not be a valid date.
您还希望确保该月份不会失效。增加过去 12 月的月份将不是有效日期。
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
