VBA - Adding Days to to Date

[Darlie247]

This is probably super simple, but I'm too new to this coding thing that I can't figure it out

I have dates (dd-mmm-yy) in Column A3 going down (dates will continue to be added) and I want a vba that will add 14 days on to the dates from Column A and calculate them correspondingly into Column E. I also want Column E to be left blank if there is no date in Column A.

Any help/explanation will be greatly appreciated!

 

[StephenCrump]

Here's one way you could do it:

Sub Test()

    Dim ws As Worksheet

    Set ws = Worksheets("Sheet1")   'say
    
    With ws.Range("E3:E" & ws.Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = "=IF(A3="""","""",A3+14)"
        .Value = .Value     'optional
        .NumberFormat = "d mmm yyyy"  'your choice
    End With

End Sub

 

[kevin9999]

If you wanted the process to happen automatically, you could try this Worksheet_Change event code (put in the Sheet code area of the sheet in question)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Dim lr As Long, i As Long, a, b
        lr = Cells.Find("*", , xlFormulas, , 1, 2).Row
        
        a = Range("A3:E" & lr)
        ReDim b(1 To UBound(a), 1 To 1)
        For i = 1 To UBound(a)
            If IsDate(a(i, 1)) Then b(i, 1) = a(i, 1) + 14
        Next i
        
        Range("E3").Resize(UBound(b)).Value = b
        Range("E3:E" & lr).NumberFormat = "dd-mmm-yy"
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub