因此，现在我有了这张Excel工作表，其中有一个最新修订日期。我已将此列命名为“ LastRevisionDate”。然后，我有一列名为“ RevisionFrequency”。所述“RevisionFrequency”包含一个下拉菜单（数据验证）由术语，Annually，Semi-Annually，和Quarterly。然后，我有一列指出“ NextRevisionDate”。
因此，我想编写一些VBA代码，该代码将从LastRevisionDate和RevisionFrequency计算出NextRevisionDate。
例如。在“ A”列中说我的RevisionFrequency为Semi-Annually，而最后的修订日期Mar-14在“ B”列中，那么我希望“ C”列中的NextRevisionDate声明9月。基本上是说，该项目每年修订两次。
因此，我想创建一个宏，其中“ C”列基于RevisionFrequency和LastRevisionDate。我知道我可以使用公式来完成此操作，但是我不断添加新项目，所以我不想继续将公式复制到每个单元格中。另外对于某些项目，它们不需要修订，如果没有LastRevisionDate，我也想有一个空白单元格。
到目前为止，我有以下代码：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Sheets(1)
'For this reference of the Column Named LastCalDate I am getting an error
If Not Intersect(Target, ws.Range("LastCalDate").Value) Is Nothing Then
Dim Lastdate As Date
Dim DueDate As Variant
Dim Frequency As String
Dim R As Variant
Dim C As Variant
Dim R1 As Variant
Dim C1 As Variant
Dim R2 As Variant
Dim C2 As Variant
R = Range("LastCalDate").Row
C = Range("LastCalDate").Column
R1 = Range("CalDueDate").Row
C1 = Range("CalDueDate").Column
R2 = Range("CalFrequency").Row
C2 = Range("CalFrequency").Column
Lastdate = Cells(R, C).Value 'Last Cal Date
DueDate = Cells(R1, C1).Value 'Cal Due Date
Frequency = Cells(R2, C2)
If Frequency = "Annually" Then
DueDate = DateAdd("mmm", 12, Lastdate)
End If
If Frequency = "Semi-Annually" Then
DueDate = DateAdd("mmm", 6, Lastdate)
End If
If Frequency = "Quarterly" Then
DueDate = DateAdd("mmm", 3, Lastdate)
End If
End Sub
到目前为止，这就是我所拥有的。我不确定我是否正确执行此操作？
使用Worksheet_Change方法是一种无需复制和粘贴公式即可创建新单元格值的好方法。我还在代码中包括检查，以确保未设置日期或频率，然后清除了该值。
Private Sub Worksheet_Change(ByVal Target As Range)
' declare and set worksheet
Dim ws As Worksheet
Set ws = Sheets(1)
' declare and set default date
Dim DefaultDueDate As Date
' declare needed variables
Dim StartDate As Date
Dim Frequency As String
Dim DueDate As Date
' make sure the change only occured on the "A" or "B" column
If Target.Column = 1 Or Target.Column = 2 Then
StartDate = ws.Range("A" & Target.Row)
Frequency = ws.Range("B" & Target.Row)
' if start date does not equal the default due date and the frequency is not blank, set due date variable
If StartDate <> DefaultDueDate And Frequency <> "" Then
' add months to the provided start date
If Frequency = "Annually" Then
DueDate = DateAdd("m", 12, StartDate)
ElseIf Frequency = "Semi-Annually" Then
DueDate = DateAdd("m", 6, StartDate)
ElseIf Frequency = "Quarterly" Then
DueDate = DateAdd("m", 3, StartDate)
End If
' Make sure frequency selection is correct and due date was set
If DueDate <> DefaultDueDate Then
ws.Range("C" & Target.Row) = DueDate
End If
Else
' clear Next Revision Date when Frequency or Start Date is blank
ws.Range("C" & Target.Row) = ""
End If
End If
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句