We help IT Professionals succeed at work.

Fill Dates series by week based on Entered Staret and End date.

SMP319
SMP319 asked
on
I am looking for a code to enter dates into a column based on dates entered in Start and End Date Cells. The dates will need to be the friday date for every week.

Sample File attached. this needs to be automated. not the simple fill in the next date and use the auto fill option in excel

Thanks

Comment
Watch Question

Top Expert 2008

Commented:
Can you attach the file?

Author

Commented:
Thought I did. Here it is
Auto-Fill-Example.xlsm
Rob HensonFinance Analyst

Commented:
Formula in E4:

=IF(E3="","",IF(E3+7>$B$4,"",E3+7))

Where E3 is start date (linked to B3 if required) and B4 is finish date.

Fill down as many rows as required, when beyond finish date result will be blank.

Thanks
Rob H
Top Expert 2008
Commented:
Something like this. This will put the series starting in the selected cell

Sub AutofillDates()
'
' AutofillDates Macro
'
Dim dtStart As Date, dtEnd As Date

dtStart = [b3]
dtEnd = [b4]

Selection.Value = dtStart
Selection.Offset(1) = dtStart + 7

Selection.Resize(2).AutoFill Destination:=Range(Selection, Selection.Offset((dtEnd - dtStart) / 7)), Type:=xlFillDefault

End Sub

Open in new window


Thomas
Analyst Assistant
Commented:
There are various ways to do it.

Here's one using DataSeries:
Dim NoFridays As Long

    NoFridays = Int((Range("B4").Value - Range("b3").Value) / 7) + 1
    
     Range("B3").Copy Range("E3")
     
    
    Range("E3").Resize(NoFridays).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
        xlDay, Step:=7, Trend:=False

Open in new window

Author

Commented:
Nutsch. Can you update the code to select a specific cell instead of just Selection?

Author

Commented:
Thanks to all for the quick response
Top Expert 2008

Commented:
Yes

Sub AutofillDates()
'
' AutofillDates Macro
'
Dim dtStart As Date, dtEnd As Date

dtStart = [b3]
dtEnd = [b4]

with cells(3,"E")
   .Value = dtStart
   .Offset(1) = dtStart + 7

   .Resize(2).AutoFill Destination:=Range(.cells(1,1), .cells(1,1).Offset((dtEnd - dtStart) / 7)), Type:=xlFillDefault
end with

End Sub

Open in new window