3 week look ahead schedule

[Lewellymoss77]

if you would be so kind to offer a piece of guidance this would be great. this is my look ahead schedule for work, and i would like to be able to have the dates in these three weeks auto populate when i fill out the "week ending" box i created in cell x7. as you can see, i have it programmed to fill in the one sunday that the first week ends. ive subscribed to your channel among many others and i cant find a video that explains this. it seems to me it would be maybe an "IF" function? any help would be appreciated.

 

[johnnyL]

Your picture is very blurry so I may have misaligned some of the info. Double check and correct if need be.

I came up with some code that you should do what you want. Right click the sheet tab that you want to run it on, select 'View Code' and then paste the code into the big window that pops up. Then go back to the sheet and try it out.

Private Sub Worksheet_Change(ByVal Target As Range)
'
    Dim OriginalEndingDate      As String
    OriginalEndingDate = "X7"                                                               ' <--- Set this to the address contining the Week Ending Date
'
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
'
    If Not Intersect(Target, Range(OriginalEndingDate)) Is Nothing Then
'
        Dim BeginDate               As Date
        Dim DateIncrementer         As Long
        Dim DaysRow                 As Long
        Dim FirstStartDaysColumn    As String, SecondStartDaysColumn    As String, ThirdStartDaysColumn As String
'
        FirstStartDaysColumn = "Y"                                                          ' <--- set this to the start column of the first set of days
        SecondStartDaysColumn = "AP"                                                        ' <--- set this to the start column of the second set of days
        ThirdStartDaysColumn = "BH"                                                         ' <--- set this to the start column of the third set of days
        DaysRow = 14                                                                        ' <--- Set this to the row for displaying the days generated
'
'
'
        BeginDate = DateAdd("d", -6, CStr(Range(OriginalEndingDate)))                       ' Calculate the Beginning Date to enable day incrementing
'
        For DateIncrementer = 0 To 20                                                       ' Loop to increment the sets of Days
            Select Case DateIncrementer
                Case Is < 7                                                                 ' Display the first 7 days
                    Cells(DaysRow, Range(FirstStartDaysColumn & 1).Column + DateIncrementer) = Day(DateAdd("d", DateIncrementer, CStr(BeginDate)))
                Case 7 To 13                                                                ' Display the second 7 days
                    Cells(DaysRow, Range(SecondStartDaysColumn & 1).Column - 7 + DateIncrementer) = Day(DateAdd("d", DateIncrementer, CStr(BeginDate)))
                Case 14 To 20                                                               ' Display the third 7 days
                    Cells(DaysRow, Range(ThirdStartDaysColumn & 1).Column - 14 + DateIncrementer) = Day(DateAdd("d", DateIncrementer, CStr(BeginDate)))
            End Select
        Next
    End If
End Sub

 

[Lewellymoss77]

where here will the program know the dates of the year as far as how many days are in each month for the year that is input into x7?

 

[johnnyL]

It's Magic!
Try it and see.

 

[Lewellymoss77]

I am as far as "Begin Date". Could you please explain how I calculate the begin date and where to put that and what format to type it in? AND the subsequent instructions "loop to increment and where to put that and what format to type it as? ALSO where to put the next 7 days and how to format/type those in? this is a learning experience and I Really appreciate this.

 

[johnnyL]

Post #2 in this thread tells you exactly what you need to do. The code I provided does all the calculations for you.

 

[Fluff]

If your happy with a formula, put this in the cell for the Monday

=SEQUENCE(,7,X7-6)

and clear the other cells for that week & it will spill across

 

[Lewellymoss77]

Post #2 in this thread tells you exactly what you need to do. The code I provided does all the calculations for you.

what i am asking is, do i put the date as #/##/#### or do i just put the day? what im tryin to say is that i do not understand the language after the "begin date" part and asking if someone could elaborate

 

[Lewellymoss77]

what i am asking is, do i put the date as #/##/#### or do i just put the day? what im tryin to say is that i do not understand the language after the "begin date" part and asking if someone could elaborate

i am trying to make this so i can distribute this document and it will auto populate for anyone at any future time.

 

[johnnyL]

You put the date in X7 just like you have in the first post here. 11/28/2021 for example