#1
|
|||
|
|||
Excel to auto populate dates based on start and end
I would like to create a template using an excel spreadsheet where we can calculate monthly project costs. 1. I have provided the start date and the end date of the project. 2. I have provided the formula for the monthly cost. I would like Excel to auto populate the end of month date beginning with my start date (by row) and to automatically stop when it reaches the end date. How can this be done? Does this need to be a macro? Thanks, Pati Doherty |
#2
|
||||
|
||||
It could be done via formulae, provided you were prepared to tie up however many rows the maximum project length is; otherwise a macro would be required. An alternative is to input the first two months' dates & amounts in A1:A2 & B1:B2, then select the lot and drag down as far as needed.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks for the response, but I think I do want to create a macro. I want the macro to auto-fill the end of month dates in rows. I would like to provide a start and end date and then have the macro automatically fill the number of rows needed based on a monthly end of month date. I started a spreadsheet and created an "Input" tab that would keep my variables and then for now I manually copied down my months to show how I would ultimately want it to look. Any help with the macro (including a button for someone to push) would be great! I've attached my spreadsheet I have started with.
|
#4
|
||||
|
||||
Your original post refers to:
1. the start date and the end date of the project. 2. the formula for the monthly cost. Your attached workbook shows not only (1) but numerous other dates as well and no indication of (2). I assume (2) is one of A, B, C, D or V, but I can't tell which. It is also not apparent where the output is to go. Your O&M sheet only lists annual periods, not the monthly periods you mentioned.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
I manually populated the spreadsheet by dragging the formula to the bottom. In this instance, I would use the Construction Start Date and the Operations End Date, but either way I don't know how to build a macro to populate down a spreadsheet with dates like the example I provided. Sorry if the other fields added confusion, don't worry about the other fields, I am only interested in start and end populating down in rows and auto stopping when it reaches the Operations End Date. Thanks!
|
#6
|
||||
|
||||
That's all very well, but your interval dates are based on years, not months. So which is it to be?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Let's use a start date of January 1, 2014 and an end date of 12/31/2048. Now let's show the last month of the year, one row for each year until it stops automatically in 2048. Thank you!
|
#8
|
||||
|
||||
Try adding the following macro to your 'Inputs' worksheet's code module:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim StartDate As Date, Duration As Long, i As Long With ActiveSheet If Intersect(Target, .Range("D9:D11")) Is Nothing Then Exit Sub If ActiveSheet.Range("D9") = "" Then Exit Sub If ActiveSheet.Range("D10") < 1 Then Exit Sub StartDate = .Range("D9").Value Duration = .Range("D11").Value End With With Worksheets("O&M") .Range("A5:D" & .UsedRange.Rows.Count).ClearContents .Range("A5").Value = StartDate .Range("B5").Value = DateAdd("yyyy", 1, StartDate) - 1 .Range("C" & i + 5).Value = Format(StartDate, "yyyy") .Range("D" & i + 5).Value = 1 For i = 1 To Duration - 1 .Range("A" & i + 5).Value = DateAdd("yyyy", i, StartDate) .Range("B" & i + 5).Value = DateAdd("yyyy", i + 1, StartDate) - 1 .Range("C" & i + 5).Value = Format(DateAdd("yyyy", i, StartDate), "yyyy") .Range("D" & i + 5).Value = i + 1 Next End With End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] Last edited by macropod; 04-17-2014 at 04:11 PM. Reason: Code revision |
#9
|
|||
|
|||
Thanks for your help, this is what I believe I need. Since I'm only just learning about macros, I am getting a debug error and not sure how to fix it. I've attached the new spreadsheet with the macro and also a picture of the error. Thanks again!
|
#10
|
||||
|
||||
Two problems:
1. You didn't add the macro to your 'Inputs' worksheet's code module. Instead, you created a new module and added it to that. 2. You renamed the macro, from 'Private Sub Worksheet_SelectionChange(ByVal Target As Range)' to 'Sub AutoDate()'. Both actions prevent the macro running automatically.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto Populate Word From to Excel File | webber | Word | 1 | 10-02-2013 02:52 PM |
Start / End Dates in conjunction with man days | loobs40 | Project | 5 | 09-19-2013 09:21 AM |
how to populate textbox based on combobox selection in word | IvanGeorgiev | Word | 1 | 02-21-2013 07:32 PM |
how to populate textbox based on combobox selection in word | IvanGeorgiev | Word VBA | 1 | 02-21-2013 07:28 PM |
Task Start and End Dates | OTPM | Project | 2 | 12-04-2011 09:14 AM |