Inserting lines according to number of days, adjusting date

[Guest]

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!

 

[Stopher]

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!

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

 

[Stopher]

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

 

[Guest]

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]

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?

 

[Guest]

Thanks so much. I'll try it out as soon as possible and let you know how I go.

KDJ

 

[Guest]

Hello Stopher

I am sorry - I missed your post with your comment about only having half
the code. Yes, there is something missing.

I need to go through the list and insert additional lines everytime there is
an entry where Startdate and Enddate are not equal.

So the steps are:
1. Check whether Startdate = Enddate
2. If so, do nothing
3. If not, insert number of lines = (Startdate - Enddate)

e.g.: Startdate = October 1, Enddate = October 3 -> 2 lines must be added.

4. Change the original line then fill the new lines with exactly the same
information as the first line except for the dates

Original line:
(StartDate EndDate Information Room)
October 1 October 3 Bla bla bla 301

Original line changed plus 2 additional:
October 1 October 1 Bla bla bla 301
October 2 October 2 Bla bla bla 301
October 3 October 3 Bla bla bla 301

Thank you so much!!!
KDJ

 

[Stopher]

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

 

[Guest]

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

 

[Guest]

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

 

[Stopher]

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.

 

[Stopher]

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

 

[Stopher]

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

 

[Guest]

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