All Mondays in a date range

[Chunk]

I have a column on a worksheet that I want to populate with all of the Mondays in a user defined date range. The user will entering the dates via a textbox (tb_SP and tb_EP). When the Create Project button is clicked the dates will populate the column. Any idea of where to start? Thanks in advance.

V/R,

Chunk

[SamT]

A "Start" for your perusal

For i = tb_SP to tb_EP
If WeekDay(i) = 2 (or 1, depending on yout serttings) then
Rw = Rw + 1
Cells(Rw, Col) = i

[mdmackillop]

Sub Test()
Dim tb_SP, tb_EP, x
tb_SP = CDate(InputBox("Start date", "First Monday"))
tb_EP = CDate(InputBox("End date", "Last Monday "))
x = Int(tb_EP - tb_SP) / 7 + 1
With ActiveCell
.Value = tb_SP
.Resize(x).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlWeekday, Step:=5
.Resize(x).NumberFormat = "ddd dd/mm/yy"
End With
End Sub

[Chunk]

Better description: In the attached spreadsheet I show the date range for the project (ProjectDates) and the Report Dates (Mondays). On a userform (UserForm1), the user will type into two text boxes Start Project (tb_SP) and End Project (tb_EP). What I need is to have a worksheet column populated with only the Report Dates (Mondays). If the tb_EP date is not a Monday, I need the next Monday entered as well. I hope that explains it better.

Chunk

[Chunk]

Duh, here's the spreadsheet
19192

[mdmackillop]

Always best to post a sample workbook to start with. :thumb

Private Sub CommandButton1_Click()
Dim x, c, cel
x = CDate(tb_EP) - CDate(tb_SP) + 6
Set c = Columns(1).Cells.Find(CDate(tb_SP)).Resize(x)
For Each cel In c
If Weekday(cel) = 2 Then
With cel.Offset(, 2)
.Value = cel
.NumberFormat = "ddd dd/mm/yy"
End With
End If
Next cel
End Sub

[Chunk]

I am receiving a "Run-time error '91': Object variable or With block variable not set" error. on the following line:

Set c = Columns(1).Cells.Find(CDate(tb_SP)).Resize(x)
Any ideas?


Also, worksheet wont have the full range of dates. Col A was added as an example to show all of the dates between start and finish. Only the start and finish date are provided by the user. Sorry for any confusion.
Chunk

[mdmackillop]

Try date in full dd/mm/yy format.
This works for me

[Chunk]

The code does work, but alas, I think my description of what I wanted to do was lacking.

The user is going to start with a blank worksheet. I am trying to get the Report Dates (Monday Dates) to populate the column based on the user input.

With my limited skill, it would have to sift through the date range given by the user and spit out the Monday dates to the worksheet.
(If the tb_EP date is not a Monday, I need the next Monday entered as well.)

Does this help, or did I just confuse the matter?

Chunk

[mdmackillop]

I don't think Spit is a valid VBA command.
Given 2 dates, 22/05/17, 05/07/17, what output do you expect to see? Please post example.

[Chunk]

Lol.......I was going to try it though.

With SP (05/17/17) and EP (06/30/17)

05/22/17
05/29/17
06/05/17
06/12/17
06/19/17
06/26/17
07/03/17

[Chunk]

mm/dd/yy format

[mdmackillop]

Where does this result go? Active cell, Specified cell, Next empty cell?

[Chunk]

L3 would be the first entry in the column

[mdmackillop]

Private Sub CommandButton1_Click()
Dim d As Date
Range("L3:L50").ClearContents 'adjust to suit
For d = CDate(tb_Sp) To CDate(tb_EP) + 6
If Weekday(d) = 2 Then
Range("L3").Offset(i).Value = d
i = i + 1
End If
Next
Range("L3").Resize(i).NumberFormat = "ddd mm/dd/yy" 'adjust to suit
End Sub

[SamT]

Well, that looks familiar. :D

[mdmackillop]

Standing on the shoulders of giants!

[SamT]

Yes, I have learned sooo much from the guys and girls here.

[Chunk]

Works like a dream.....thank you (both) for your effort in guiding the blind.

When I figure out that "Spit" command, I'll let you know.

Respectfully,

Chunk