Home>
Calculates 10 business days, 20 business days, 30 business days and dates after entering the date in the input column (R, S, U, W) in the specified column (T, V, X) We are creating a macro event to let you.
(Example) If today's date is entered in the R column, the date after 10 business days for the T column, the date after 20 business days for the V column, and the date after 30 business days for the X column are automatically entered.
Deleting R column (blank) automatically deletes subsequent columns
Here is the code
Private Sub Worksheet_Change (ByVal Target As Range)
Dim Yasumi
Dim i
Dim j
Dim k
Dim Rng As Range
Set Yasumi = ThisWorkbook.Worksheets (2)
'Calculate the date every business day
i = Application.WorkDay (Target.Value, 10, Yasumi.Range ("A2: A81"))
j = Application.WorkDay (Target.Value, 20, Yasumi.Range ("A2: A81"))
k = Application.WorkDay (Target.Value, 30, Yasumi.Range ("A2: A81"))
Application.EnableEvents = False
For Each Rng In Target
'When the scheduled date is in column R, enter the date 10 business days after in column T.
If IsDate (Rng.Value) = True And Rng.Column = 18 And Rng.Row<>1 Then
    Rng.Offset (0, 2) .Value = i
    Rng.Offset (0, 2) .NumberFormatLocal = "yyyy/mm/dd"
'When the date in column R is deleted, the date in column T is also deleted
    ElseIf Rng.Value = "" And Rng.Column = 18 And Rng.Offset (0, 2)<>"" And Rng.Row<>1 Then
        Rng.Offset (0, 2) .Value = ""
        Rng.Offset (0, 4) .Value = ""
        Rng.Offset (0, 6) .Value = ""
End If
'When the actual date is in column S, enter the date 10 business days after in column T
If IsDate (Rng.Value) = True And Rng.Column = 19 And Rng.Row<>1 Then
    Rng.NumberFormatLocal = "YYYY/MM/DD"
    Rng.Offset (0, 1) .Value = iRng.Offset (0, 1) .NumberFormatLocal = "yyyy/mm/dd"
'When the date in column S is deleted, the date in column T/V/W is also deleted (the date in column R is not deleted)
    ElseIf Rng.Value = "" And Rng.Column = 19 And Rng.Offset (0, 1)<>"" And Rng.Row<>1 Then
        Rng.Offset (0, 1) .Value = ""
        Rng.Offset (0, 3) .Value = ""
        Rng.Offset (0, 5) .Value = ""
End If

'When there is an appointment in row T, leave 20 business days in row V
If IsDate (Cells (Rng.Row, "T")) = True And Rng.Row<>1 Then
    Cells (Rng.Row, "V"). Value = j
    Cells (Rng.Row, "V"). NumberFormatLocal = "yyyy/mm/dd"
'When the date in column T is deleted, the date in column V/W is also deleted
    ElseIf Rng.Value = "" And Rng.Column = 20 And Rng.Row<>1 Then
        Rng.Offset (0, 2) .Value = ""
        Rng.Offset (0, 4) .Value = ""
End If

'When the actual date is entered in the U column, enter 20 business days later in the V column.
If IsDate (Cells (Rng.Row, "U")) = True And Rng.Row<>1 Then
    Cells (Rng.Row, "V"). Value = j
    Cells (Rng.Row, "V"). NumberFormatLocal = "yyyy/mm/dd"
'When the date in column U is deleted, the date in column V/X is also deleted
    ElseIf Rng.Value = "" And Rng.Column = 21 And Rng.Row<>1 Then
        Rng.Offset (0, 1) .Value = ""
        Rng.Offset (0, 3) .Value = ""
End If

'When the scheduled date is in row V, leave 30 business days in row X
If IsDate (Cells (Rng.Row, "V")) = True And Rng.Row<>1 Then
   Cells (Rng.Row, "X"). Value = k
   Cells (Rng.Row, "X"). NumberFormatLocal = "yyyy/mm/dd"'If the date in column V is deleted, delete the date in column X
    ElseIf Rng.Value = "" And Rng.Column = 22 And Rng.Row<>1 Then
        Rng.Offset (0, 2) .Value = ""
End If

'When the actual date is in row W, enter 30 business days in row X
If IsDate (Cells (Rng.Row, "W")) = True And Rng.Row<>1 Then
    Cells (Rng.Row, "X"). Value = k
    Cells (Rng.Row, "X"). NumberFormatLocal = "yyyy/mm/dd"
'If the date in column W is deleted, the date in column X is also deleted
    ElseIf Rng.Value = "" And Rng.Column = 23 And Rng.Row<>1 Then
        Rng.Offset (0, 1) .Value = ""
End If
Next
Application.EnableEvents = True
End Sub
Problem i am experiencing
When the Delete button is pressed in the V column, the X column should be deleted automatically, but for some reason the date is entered.
As of today, the V column is "1900/1/27" and the X column is "1900/2/10" as of today.
Even if the Delete button is pressed in the X column, it is not deleted and the above date is still entered.
Applicable source code
'When the scheduled date is in column V, enter 30 business days in column X
If IsDate (Cells (Rng.Row, "V")) = True And Rng.Row<>1 Then
   Cells (Rng.Row, "X"). Value = k
   Cells (Rng.Row, "X"). NumberFormatLocal = "yyyy/mm/dd"
'If the date in column V is deleted, delete the date in column X
    ElseIf Rng.Value = "" And Rng.Column = 22 And Rng.Row<>1 Then
        Rng.Offset (0, 2) .Value = ""
End If
For example, if you press the Delete button in the U row, it will work without problems, and the previous row (R etc.) will be deleted without any problems.
  • Answer # 1

    There is noRng.Column = xxin the conditional expression after "When the schedule is in the T column".