Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
Hello
I am using Excel 2003. I have a table showing meetings. This includes StartDate, EndDate, Information (about the meeting) and Room. In most cases, the StartDate=EndDate. However, for those meetings that take more than one day, I need to have an entry for each day so that a daily schedule of "what's on" can be printed. I need to insert additional lines for the additional days, and copy the information from the original entry into the new lines. I then have to change the StartDate and EndDate for each day. Example- if the meeting started on October 1 and finished October 3, the original entry is: StartDate EndDate Information Room October 1 October 3 Bla bla bla 301 The new entries should be: October 1 October 1 Bla bla bla 301 October 2 October 2 Bla bla bla 301 October 3 October 3 Bla bla bla 301 I have written the rest ... sorting by StartDate and grouping according to this. Any assistance for adding the lines, copying the data and changing the date would be greatly appreciated! -- Thanks very much. KDJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
KDJ wrote: Hello I am using Excel 2003. I have a table showing meetings. This includes StartDate, EndDate, Information (about the meeting) and Room. In most cases, the StartDate=EndDate. However, for those meetings that take more than one day, I need to have an entry for each day so that a daily schedule of "what's on" can be printed. I need to insert additional lines for the additional days, and copy the information from the original entry into the new lines. I then have to change the StartDate and EndDate for each day. Example- if the meeting started on October 1 and finished October 3, the original entry is: StartDate EndDate Information Room October 1 October 3 Bla bla bla 301 The new entries should be: October 1 October 1 Bla bla bla 301 October 2 October 2 Bla bla bla 301 October 3 October 3 Bla bla bla 301 I have written the rest ... sorting by StartDate and grouping according to this. Any assistance for adding the lines, copying the data and changing the date would be greatly appreciated! -- Thanks very much. KDJ Just right a sub that takes the end date and subtracts the startdate to work out how many times it need to loop to insert the new rows. Take the start date and add the counter to it to get the new start and end date for fields 1 and 2 and then copy the room from the original cell. I started to write the code and then couldn't rember the syntax for offset and then realised you prob want date formatting etc. Will write the code and test it and hopefully I'll post back before someone else does, just a warning it might not look pretty. Stopher |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
Try this:
Sub InsertRows() Dim Startdate As Date Dim EndDate As Date Dim Gap Dim Prow As Integer Startdate = Range("A2").Value EndDate = Range("B2").Value Gap = EndDate - Startdate Prow = 4 If Gap = 0 Then Else For i = 0 To Gap Let Startdate = Startdate + i Let Prow = Prow + 1 Cells(Prow, 1) = Startdate Cells(Prow, 2) = Startdate Cells(Prow, 4) = Range("D2") Startdate = Range("A2").Value Next End If End Sub Just change the Prow to your starting row. It also assumes that the Startdate, Enddate line starts in A2. You could curcumvent this by giving the cells named ranges and then you could move them anywhere, then just sub these in for Range("A2").value. Hope this helps you out. Stopher |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
The code would be very much appreciated! I had offset and the counter in my
head but just can't put it together. I am too much of a novice. The formatting is not a problem. I can solve that at the end by formatting the report as a whole. Getting those lines in is where I am stuck. Thank you! KDJ "Stopher" wrote: KDJ wrote: Hello I am using Excel 2003. I have a table showing meetings. This includes StartDate, EndDate, Information (about the meeting) and Room. In most cases, the StartDate=EndDate. However, for those meetings that take more than one day, I need to have an entry for each day so that a daily schedule of "what's on" can be printed. I need to insert additional lines for the additional days, and copy the information from the original entry into the new lines. I then have to change the StartDate and EndDate for each day. Example- if the meeting started on October 1 and finished October 3, the original entry is: StartDate EndDate Information Room October 1 October 3 Bla bla bla 301 The new entries should be: October 1 October 1 Bla bla bla 301 October 2 October 2 Bla bla bla 301 October 3 October 3 Bla bla bla 301 I have written the rest ... sorting by StartDate and grouping according to this. Any assistance for adding the lines, copying the data and changing the date would be greatly appreciated! -- Thanks very much. KDJ Just right a sub that takes the end date and subtracts the startdate to work out how many times it need to loop to insert the new rows. Take the start date and add the counter to it to get the new start and end date for fields 1 and 2 and then copy the room from the original cell. I started to write the code and then couldn't rember the syntax for offset and then realised you prob want date formatting etc. Will write the code and test it and hopefully I'll post back before someone else does, just a warning it might not look pretty. Stopher |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
I think the code I posted is half what you wanted sorry, I read your
post again. What you want it to do is keep adding rows to the bottom of the list everytime you change the dates and run the macro, correct? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
Thanks so much. I'll try it out as soon as possible and let you know how I go.
KDJ "Stopher" wrote: Try this: Sub InsertRows() Dim Startdate As Date Dim EndDate As Date Dim Gap Dim Prow As Integer Startdate = Range("A2").Value EndDate = Range("B2").Value Gap = EndDate - Startdate Prow = 4 If Gap = 0 Then Else For i = 0 To Gap Let Startdate = Startdate + i Let Prow = Prow + 1 Cells(Prow, 1) = Startdate Cells(Prow, 2) = Startdate Cells(Prow, 4) = Range("D2") Startdate = Range("A2").Value Next End If End Sub Just change the Prow to your starting row. It also assumes that the Startdate, Enddate line starts in A2. You could curcumvent this by giving the cells named ranges and then you could move them anywhere, then just sub these in for Range("A2").value. Hope this helps you out. Stopher |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
That is exactly it.
The list can easily be on another worksheet. I can sort, group and format it there too. I know the code for that. Thank you! Regards KDJ "Stopher" wrote: So from a list like: October1 October3 October4 October4 October5 October6 when you run the macro the output would be: October1 October1 October2 October2 October3 October3 October4 October4 October5 October5 October6 October6 With all the extra info, correct? Does it matter if this new list is on the same sheet or can this be recreated on a new sheet? Stopher |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
Hello Stopher
I am not sure if I confused you with my last answer. The new lines can be on another worksheet, yes. Any feedback on how to get it there? I would be really keen to hear more.... Thank you and kind regards KDJ "KDJ" wrote: That is exactly it. The list can easily be on another worksheet. I can sort, group and format it there too. I know the code for that. Thank you! Regards KDJ "Stopher" wrote: So from a list like: October1 October3 October4 October4 October5 October6 when you run the macro the output would be: October1 October1 October2 October2 October3 October3 October4 October4 October5 October5 October6 October6 With all the extra info, correct? Does it matter if this new list is on the same sheet or can this be recreated on a new sheet? Stopher |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
Sorry have been away from work, and now that I'm back I'm snowed with
document control and proceedual bonanza for NATA assesment. Will try to look later in the week but basically the same loop i performed before except paste to sheet2 instead of range(xx) on sheet1. this way you can have your list quite long and still paste on the other sheet without interference. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
Sub InsertRows()
Dim Startdate As Date Dim EndDate As Date Dim Gap Dim Prow As Integer Dim Cref As Integer Sheets("Sheet1").Activate Range("A1").Select Cref = 1 Do Until ActiveCell.Value = "" Startdate = ActiveCell.Value ActiveCell.Offset(0, 1).Activate EndDate = ActiveCell.Value Gap = EndDate - Startdate For i = 0 To Gap Let Prow = Prow + 1 Sheets("Sheet2").Cells(Prow, 1) = Startdate Sheets("Sheet2").Cells(Prow, 2) = Startdate Sheets("Sheet2").Cells(Prow, 3) = ActiveCell.Offset(0, 1).Value Sheets("Sheet2").Cells(Prow, 4) = ActiveCell.Offset(0, 2).Value Startdate = Startdate + 1 Next Cref = Cref + 1 Cells(Cref, 0).Select Loop End Sub Try that, probably smarter ways of doing it but if someone wants to butcher it then fine with me. The code assumes that your dates start in A1. If not change the Range to the starting cell and Cref to the corrsponding row number. Regards Stopher |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
Made a mistake, this
Cells(Cref, 0).Select should be Cells(Cref, 1).Select so.. Sub InsertRows() Dim Startdate As Date Dim EndDate As Date Dim Gap Dim Prow As Integer Dim Cref As Integer Sheets("Sheet1").Select Range("A1").Select Cref = 1 Do Until ActiveCell.Value = "" Startdate = ActiveCell.Value ActiveCell.Offset(0, 1).Activate EndDate = ActiveCell.Value Gap = EndDate - Startdate For i = 0 To Gap Let Prow = Prow + 1 Sheets("Sheet2").Cells(Prow, 1) = Startdate Sheets("Sheet2").Cells(Prow, 2) = Startdate Sheets("Sheet2").Cells(Prow, 3) = ActiveCell.Offset(0, 1).Value Sheets("Sheet2").Cells(Prow, 4) = ActiveCell.Offset(0, 2).Value Startdate = Startdate + 1 Next Cref = Cref + 1 Cells(Cref, 1).Select Loop End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting lines according to number of days, adjusting date
Thank you, Stopher!!!!
I have been on vacation ... sorry for the late reply. I will test it asap and let you know how it went. I appreciate it very, very much!!!! KDJ "Stopher" wrote: Made a mistake, this Cells(Cref, 0).Select should be Cells(Cref, 1).Select so.. Sub InsertRows() Dim Startdate As Date Dim EndDate As Date Dim Gap Dim Prow As Integer Dim Cref As Integer Sheets("Sheet1").Select Range("A1").Select Cref = 1 Do Until ActiveCell.Value = "" Startdate = ActiveCell.Value ActiveCell.Offset(0, 1).Activate EndDate = ActiveCell.Value Gap = EndDate - Startdate For i = 0 To Gap Let Prow = Prow + 1 Sheets("Sheet2").Cells(Prow, 1) = Startdate Sheets("Sheet2").Cells(Prow, 2) = Startdate Sheets("Sheet2").Cells(Prow, 3) = ActiveCell.Offset(0, 1).Value Sheets("Sheet2").Cells(Prow, 4) = ActiveCell.Offset(0, 2).Value Startdate = Startdate + 1 Next Cref = Cref + 1 Cells(Cref, 1).Select Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping row number after inserting lines | Excel Worksheet Functions | |||
Inserting new lines with corresponding number | Excel Discussion (Misc queries) | |||
inserting new lines with number | Excel Discussion (Misc queries) | |||
inserting rows without adjusting formula relationships | Excel Discussion (Misc queries) | |||
2 questions - one about inserting the date, other about adding lines. | New Users to Excel |