Macro Question - How to incrementally add a count?

[Bigtime]

Current Output:

[TABLE="width: 257"]
<tbody>[TR]
[TD="align: right"]Monday, January 18, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Wednesday, January 20, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Friday, January 22, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Monday, January 25, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Wednesday, January 27, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Friday, January 29, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Monday, February 01, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Wednesday, February 03, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Friday, February 05, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Monday, February 08, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Wednesday, February 10, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Friday, February 12, 2016[/TD]
[/TR]
</tbody>[/TABLE]

Desired Output:

[TABLE="width: 257"]
<tbody>[TR]
[TD="align: right"]Week 1
Monday, January 18, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Wednesday, January 20, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Friday, January 22, 2016
Week 2[/TD]
[/TR]
[TR]
[TD="align: right"]Monday, January 25, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Wednesday, January 27, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Friday, January 29, 2016
Week 3[/TD]
[/TR]
[TR]
[TD="align: right"]Monday, February 01, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Wednesday, February 03, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Friday, February 05, 2016
Week 4[/TD]
[/TR]
[TR]
[TD="align: right"]Monday, February 08, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Wednesday, February 10, 2016[/TD]
[/TR]
[TR]
[TD="align: right"]Friday, February 12, 2016[/TD]
[/TR]
</tbody>[/TABLE]

Current code:

//thanks to [URL="http://www.mrexcel.com/forum/members/diddi.html"]diddi[/URL][COLOR=#574123] from an old post[/COLOR]
Sub MakeDatesVertical()    Dim Start As Date
    Start = DateSerial(2016, 1, 18)
    Cells(1, 1) = Start
    Cells(2, 1) = Start + 2
    Cells(3, 1) = Start + 4
    For Row = 4 To 100 Step 3
        Cells(Row, 1) = Cells(Row - 3, 1) + 7
        Cells(Row + 1, 1) = Cells(Row - 2, 1) + 7
        Cells(Row + 2, 1) = Cells(Row - 1, 1) + 7
    Next Row
End Sub

What changes need to happen so I can get the Weeks numbered?

Thanks in advance!

 

[MarkReddell]

Can just use the weeknum function? Like: =WEEKNUM(A2,2)-3
=1. Provided that your beginning week is on Mondays! Date in col. A & weeknum function in col. B

 

[Bigtime]

Thanks for your reply. I can't quite figure out how to get that working properly even though I pasted that code into col. B.

I'd love to see how that macro could be edited to get the desired output if any can shed some light on that.

Thanks.

 

[hwong2004]

Hello! Please try below solution:


Sub MakeDatesVertical()
Dim Start As Date
Dim MyWeek As Integer
Start = DateSerial(2016, 1, 18)
MyWeek = 1
Cells(1, 1) = "Week " & MyWeek
Cells(2, 1) = Start
Cells(3, 1) = Start + 2
Cells(4, 1) = Start + 4
For Row = 5 To 100 Step 4
MyWeek = MyWeek + 1
Cells(Row, 1) = "Week " & MyWeek
Cells(Row + 1, 1) = Cells(Row - 3, 1) + 7
Cells(Row + 2, 1) = Cells(Row - 2, 1) + 7
Cells(Row + 3, 1) = Cells(Row - 1, 1) + 7
Next Row
End Sub

 

[MarkReddell]

I don't believe a macro is needed here when a worksheet function can be used instead. If ALL of your dates are in col. A, Then the function: =weeknum(a2,1)-3 if Sunday is your first day of week. If it is Monday instead then: =weeknum(a2,2)-3. Then copy & paste down col. B or whenever you decide to put it!!! I hope this helps you!