Macro to Autofill Dates between Start Date and End Date

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
heres half the solution...

Code:
Sub FillCal()
    Dim StartD As Date, EndD As Date
    
    StartD = Range("D5")
    EndD = Range("D7")
    
    For Row = 10 To EndD - StartD
        Cells(Row, 4) = StartD + Row - 1
        If Day(Cells(Row, 4)) > 14 And Day(Cells(Row, 4)) < 22 Then
            If Weekday(Cells(Row, 4)) = 4 Then Cells(Row, 5) = "3rd Wednesday"
        End If
    Next Row
    
End Sub
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
and heres part 2
Code:
Sub FillCal()
    Dim StartD As Date, EndD As Date
    
    StartD = Range("D5")
    EndD = Range("D7")
    
    For Row = 10 To EndD - StartD
        Cells(Row, 4) = StartD + Row - 1
        If Day(Cells(Row, 4)) > 14 And Day(Cells(Row, 4)) < 22 Then
            If Weekday(Cells(Row, 4)) = 4 Then Cells(Row, 5) = "3rd Wednesday"
        End If
        If Day(Cells(Row, 4)) = 1 Then
            If Weekday(Cells(Row, 4)) > 2 Then
                Cells(Row - 1, 6) = "Last weekday"
            ElseIf Weekday(Cells(Row, 4)) = 1 Then
                Cells(Row - 2, 6) = "Last weekday"
            ElseIf Weekday(Cells(Row, 4)) = 2 Then
                Cells(Row - 3, 6) = "Last weekday"
            End If
        End If
    Next Row
    
End Sub
 

AndreDo

New Member
Joined
Nov 4, 2019
Messages
7
This is what I am starting with..

Actually with the below, I am only returning 11 results for

Start Date8/23/2013
Days 20.00
End Date 9/12/2013

<tbody>
</tbody>

Code:
Sub FillCal()
    Dim StartD As Date, EndD As Date
    
    StartD = Range("D5")
    EndD = Range("D7")
    
    For Row = 10 To EndD - StartD
        Cells(Row, 4) = StartD + Row - 1
    Next Row
    
End Sub
Hi Team, this does exactly what i'm trying to do but how do I make the code to populate only month end dates? I can seem to figure this out for the last three days :(
 

Watch MrExcel Video

Forum statistics

Threads
1,089,916
Messages
5,411,212
Members
403,349
Latest member
Nikojoer

This Week's Hot Topics

Top