Excel - Populate Columns with dates based on User Input

Question

I'm reasonably proficient with Excel VBA (with lots of online help!!) ... but I've stumbled into a problem that has me completely stumped.

I'm creating a Gantt Chart for project planning. My problem is that I wish to have the user specify a project start date and a project end date then have the spreadsheet populate columns from the start date to the end date.

See basic layout: enter image description here

I have already bottomed out the User Input with InputBoxes

Sub Set_Project_Start_Date()

' Written 2nd August 13
' P.J. Callaghan
'

ActiveSheet.Select
Dim projStartDate

showInputBox_Start:
projStartDate = Application.InputBox("Please enter Project Start Date" & Chr(10) & "Must be a Monday" & Chr(10) & "Format is: dd/mm/yyyy")

' Set Message Box such that clicking cancel ends the sub-routine for projStartdate variable
If projStartDate = False Then
MsgBox "You clicked the Cancel button, Input Box will close.", 64, "Cancel was clicked."
Exit Sub
ElseIf projStartDate = "" Then
MsgBox "You must click Cancel to exit.", 48, "You clicked Ok but entered nothing."
GoTo showInputBox_Start
Else
MsgBox "You entered " & projStartDate & ".", 64, "Please click OK to resume."
Range("c6").Select
With Selection
.Value = projStartDate
.NumberFormat = "dd-mmm-yy"
End With
Range("e10").Select
With Selection
.Value = projStartDate
.NumberFormat = "dd-mmm-yy"
.Orientation = 90
End With

End If

End Sub

Sub Set_Project_End_Date()

' Written 2nd August 13
' P.J. Callaghan
'

ActiveSheet.Select
Dim projEndDate

showInputBox_End:
projEndDate = Application.InputBox("Please enter Project End Date" & Chr(10) & "Must be a Monday" & Chr(10) & "Format is: dd/mm/yyyy")

' Set Message Box such that clicking cancel ends the sub-routine for projStartdate variable
If projEndDate = False Then
MsgBox "You clicked the Cancel button, Input Box will close.", 64, "Cancel was clicked."
Exit Sub
ElseIf projEndDate = "" Then
MsgBox "You must click Cancel to exit.", 48, "You clicked Ok but entered nothing."
GoTo showInputBox_End
Else
MsgBox "You entered " & projEndDate & ".", 64, "Please click OK to resume."
Range("c7").Select
With Selection
.Value = projEndDate
.NumberFormat = "dd-mmm-yy"
End With

End If

End Sub

The bit I'm stuck with is writing code to populate from the start date to the end date only. I'm sure this must be some sort of loop arrangement ... as yet I haven't figured it out.

I wondered if any of you guys could suggest a solution?

Thanks in advance,

Paul

What do you mean by "have the spreadsheet populate columns from the start date to the end date."? — Tarik
– Tarik, Commented Aug 4, 2013 at 13:01
Hi Tarik, I want the spreadsheet to populate cell e10 with the user defined project start date (variable projStartDate) then populate all subsequent columns in row 10 with each date from the project start date to the project end date. I want the macro to stop populating once the cell value reaches that of the project end date (variable projEndDate). The functionality I'm looking for is a chart which ranges from a user defined start date to a user defined end date for a project. — Paul Callaghan
– Paul Callaghan, Commented Aug 4, 2013 at 17:46

chuff · Accepted Answer · 2013-08-04 15:35:32Z

1

If your goal is to populate the row that starts with cell E10 with daily dates from the start date to the end date of the project, then you can use AutoFill:

With Worksheets("Gantt")
    Set sourceRange = .Range("E10")
    sourceRange.Value = projStartDate
    Set fillRange = .Range(sourceRange, Cells(sourceRange.Row, _
        sourceRange.Column + projEndDate - projStartDate + 1))
    sourceRange.AutoFill Destination:=fillRange, Type:=xlFillDays
End With

answered Aug 4, 2013 at 15:35

chuff

5,9061 gold badge23 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Callaghan · Accepted Answer · 2013-08-04 20:46:23Z

Chuff, that's right on the money.

I've taken your code and modified it slightly ... for some reason it didn't execute as a copy and paste

Sub Date_Fill()

' http://stackoverflow.com/questions/18043084/excel-populate-columns-with-dates-based-on-user-input

Dim projEndDate As Date
Dim projStartDate As Date
Dim projDuration As Integer

projStartDate = Range("c6").Value
projEndDate = Range("c7").Value

With Worksheets("Gantt")
    Set SourceRange = .Range("E10")
    SourceRange.Value = projStartDate
    Set fillRange = .Range(SourceRange, Cells(SourceRange.Row, _
        SourceRange.Column + projEndDate - projStartDate + 1))
    SourceRange.AutoFill Destination:=fillRange, Type:=xlFillDays
End With

End Sub

Thank you ever so much for your assistance ... this one has been bugging me for a few weeks now. I was looking into loops today as I thought the answer may lie there.

Once again ... many thanks.

Collectives™ on Stack Overflow

Excel - Populate Columns with dates based on User Input

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related