generate all sundays for the year

hi,

how do i generate all sundays for the year by just typing the year on A1
as i press enter it will generate all the sundays of the year starting on A3
with a date format of ddd-mmm-dd-yyyy

Ex: typed 2017 on A1
after pressing enter

A3 would be Sun-Jan-1-2017
A4 would be Sun-Jan-8-2017
A5 would be Sun-Jan-15-2017
and so on

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And IsNumeric(Target.Value) Then
    Dim x(), d&, i%, j%, n%
    Application.EnableEvents = 0
    i = [a1].Value
    d = Format(DateSerial(i, 1, 1), 0)
    j = Format(DateSerial(i + 1, 1, 1), 0) - d
    ReDim x(1 To Int(j / 7) + 1, 1 To 1)
    For i = 1 To j
        If d Mod 7 = 1 Then
            n = n + 1
            x(n, 1) = d
        End If
        d = d + 1
    Next
    [a3:a100].Clear
    [a3].Resize(n, 1).NumberFormatLocal = "ddd-mmm-dd-yyyy"
    [a3].Resize(n, 1) = x
    Application.EnableEvents = 1
    End If
End Sub

hth
ikboy

@haroonsid,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And IsNumeric(Target.Value) Then
    Dim x(), d&, i%, j%, n%
    Application.EnableEvents = 0
    i = [a1].Value
    d = Format(DateSerial(i, 1, 1), 0)
    j = Format(DateSerial(i + 1, 1, 1), 0) - d
    ReDim x(1 To Int(j / 7) + 1, 1 To 1)
    For i = 1 To j
        If d Mod 7 = 2 Then
            n = n + 1
            x(n, 1) = d
        End If
        d = d + 1
    Next
    [a3:a100].Clear
    [a3].Resize(n, 1).NumberFormatLocal = "ddd-mmm-dd-yyyy"
    [a3].Resize(n, 1) = x
    Application.EnableEvents = 1
    End If
End Sub

hth,
ikboy