Calculating Due Dates Excel VBA

Question

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

Answer 1

1. Add a new variable called Number to hold the value from the cell:

   Dim Number As Integer
   

2. 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
   

3. 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.