Macro to Autofill Dates between Start Date and End Date

lewis1

Board Regular
Joined
Jul 20, 2009
Messages
81
Hi, I have looked around the net, but I can't find a clear answer for the following.

I have a Start Date, eg: 08/23/2013 in Cell "D5", and
End Date; eg: 08/22/2018 in Cell "D7"

I would like a Macro which starts in Cell "D12" autofills column "D" with all the dates between and including the start and end date.

The name of the sheet I would like the macro to run on is named "Calendar"

Thanks!
Lewis.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
Code:
Sub FillCal()
    Dim StartD as Date, EndD as Date
    StartD=Calandar.Cells(5,4)
    EndD=Calandar.Cells(7,4)
    For row= 1 to endD - startd
        cells(row,4)=startD+row-1
    Next row
End sub
 

lewis1

Board Regular
Joined
Jul 20, 2009
Messages
81
Hey, this is the error I get now.

"Compile error:
Invalid qualifier"

With StartD = Calendar.Cells(5, 4)

-Calendar highlighted.

Any help would be appreciated.

Thanks
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
check the spelling of your sheet name.
 

lewis1

Board Regular
Joined
Jul 20, 2009
Messages
81
Fixed, thanks very much the code is working.

I removed the sheet reference and simply defined the ranges and that works.

Thanks again.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
too easy :)
 

lewis1

Board Regular
Joined
Jul 20, 2009
Messages
81
Hi,

I posted these additional questions in another thread, but I was directed to bring them back here.

I would like to expand the Macro to perform the following:

1. Point out the THIRD 'wednesday' of every month with a "1" in column D.
2. Point out the second last 'weekday' of every month with a "1"in column E.

Is this something anyone can help with, realize it might be too much

Also, for the first solution, if I want the macro to start in Range("C10") what adjustment should I make,.

Thanks!
Lewis.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,554
post your code so we can modify what you have and no more typos from my end
 

lewis1

Board Regular
Joined
Jul 20, 2009
Messages
81
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

<colgroup><col><col></colgroup><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
 

Forum statistics

Threads
1,077,614
Messages
5,335,253
Members
399,009
Latest member
twcaddell

Some videos you may like

This Week's Hot Topics

Top