英文:
Calculating Due Dates Excel VBA
问题
需要根据起始日期计算到期日期，但具有可变的增量。
我在这个网站上找到了以下代码，并添加了一些额外的条件。
非常有效，但是它是固定的。
如何使“Number”可变，并从单元格中获取值？
VBA新手。
英文:
Need to calculate due date based on start date but with variable increments.
I found the code below on this site and added some extra criteria.
Works very well but it is fixed.
How can the "Number" be made variable and get the values from a cell?
VBA newby.
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)
ElseIf Frequency = "Month" Then
DueDate = DateAdd("m", 1, StartDate)
ElseIf Frequency = "Week" Then
DueDate = DateAdd("ww", 1, StartDate)
ElseIf Frequency = "Day" Then
DueDate = DateAdd("d", 1, 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
答案1
得分: 4
- 添加一个新变量称为Number，用于存储单元格中的值：
Dim Number As Integer
- 从特定单元格检索“Number”的值。例如，如果包含“Number”值的单元格位于列C中，您可以使用以下代码：
Number = ws.Range("C" & Target.Row).Value
- 更新执行频率计算的代码块，以使用Number变量代替固定值：
If Frequency = "Annually" Then
DueDate = DateAdd("m", 12 * Number, StartDate)
ElseIf Frequency = "Semi-Annually" Then
DueDate = DateAdd("m", 6 * Number, StartDate)
ElseIf Frequency = "Quarterly" Then
DueDate = DateAdd("m", 3 * Number, StartDate)
ElseIf Frequency = "Month" Then
DueDate = DateAdd("m", 1 * Number, StartDate)
ElseIf Frequency = "Week" Then
DueDate = DateAdd("ww", 1 * Number, StartDate)
ElseIf Frequency = "Day" Then
DueDate = DateAdd("d", 1 * Number, StartDate)
End If
请确保调整ws.Range("C" & Target.Row)中的列引用，以匹配实际存储“Number”值的单元格。
英文:
-
Add a new variable called Number to hold the value from the cell:
Dim Number As Integer -
Retrieve the value of the "Number" from a specific cell. For example, if the cell containing the "Number" value is in column C, you can use:
Number = ws.Range("C" & Target.Row).Value -
Update the code block where the frequency calculations are performed to use the Number variable instead of the fixed values:
If Frequency = "Annually" Then DueDate = DateAdd("m", 12 * Number, StartDate) ElseIf Frequency = "Semi-Annually" Then DueDate = DateAdd("m", 6 * Number, StartDate) ElseIf Frequency = "Quarterly" Then DueDate = DateAdd("m", 3 * Number, StartDate) ElseIf Frequency = "Month" Then DueDate = DateAdd("m", 1 * Number, StartDate) ElseIf Frequency = "Week" Then DueDate = DateAdd("ww", 1 * Number, StartDate) ElseIf Frequency = "Day" Then DueDate = DateAdd("d", 1 * Number, StartDate) End If
Make sure to adjust the column reference in ws.Range("C" & Target.Row) to match the actual cell where the "Number" value is stored.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论