I'd like to write (copy) a module that converts certain rows into two rows and edits the original row. For example, I'd like to convert this:
6/1/2015 16:25 1:00
to this:
6/1/2015 16:25 23:59
6/2/2015 0:00 1:00
I've identified the rows I'd like to do this to in another column. And there are other columns in the rows that I'd like to simply copy down.
I'd appreciate any help and/or VBA tutorials you recommend to get me to a point where I could write this on my own.
This code should help:
Public Sub splittime()
Application.ScreenUpdating = False
firstrow = 2
firstcopycolumn = 4
lastcopycolumn = 7
sheetname = "Sheet1"
Dim wkb As Workbook
Dim wks As Worksheet
Set wkb = ThisWorkbook
Set wks = wkb.Sheets(sheetname)
therow = firstrow
usedrow = True
affrows = 0
While usedrow
thedate = wks.Cells(therow, 1)
If thedate <> "" Then
firstdate = wks.Cells(therow, 2)
lastdate = wks.Cells(therow, 3)
If lastdate < firstdate Then
Rows(therow + 1).Insert shift:=xlShiftDown
wks.Cells(therow + 1, 1) = thedate + 1
wks.Cells(therow + 1, 2) = "0:00"
wks.Cells(therow + 1, 3) = lastdate
wks.Range(Cells(therow + 1, firstcopycolumn), Cells(therow + 1, lastcopycolumn)).Value = wks.Range(Cells(therow, firstcopycolumn), Cells(therow, lastcopycolumn)).Value
wks.Cells(therow, 3) = "23:59"
affrows = affrows + 1
End If
therow = therow + 1
Else
usedrow = False
End If
Wend
Application.ScreenUpdating = True
themessage = MsgBox("Finished" & vbCrLf & "Affected rows: " & affrows, vbInformation)
End Sub
To use Macros follow this guide or simply open VBA/ Macros with ALT+ F11, under ThisWorkbook insert a new module and paste the code.
The variable firstrow can be adjusted to your needs.
Also, with the help of the variables firstcopycolumn and lastcopycolumn you can define which range of columns will be copied down.
Do you have a specific reason for wanting to do this in VBA? I solved a similar problem a few months ago using formulas; I have adapted that solution to your problem.
But first, let me make sure that I understand your problem. As I understand it, you have a worksheet that looks like this:
A B C D E F
1 Date Start End User Color Overnight
2 5/28/2015 15:00 21:00 Fred Green
3 6/1/2015 16:25 1:00 Henry Red 1
4 6/4/2015 9:00 13:00 Mary Blue
where each row represents an event, with a date, start time, end time,
and other data. Some of the events occur overnight
(i.e., they begin before midnight and end after midnight).
You have marked those events with a 1 in Column F
(although there’s no need to do this manually;
you could simply set F2 to =B2>C2 and drag down).
No event lasts more than 24 hours, so there’s no possibility
of an event starting at 10:00 on Monday and ending at 11:00 on Tuesday
(or, if there is, you have some way of identifying those events
that you haven’t described, and you have set Column F appropriately).
In any case, no event spans more than two days
(e.g., you will never have an event beginning at 23:00 on Monday
and running until 1:00 on Wednesday).
You want to split each multi-day (i.e., overnight) event into two rows:
one on the first day, from the start time until midnight (or 23:59),
and another on the second day, from midnight until the end time.
You want all the other data associated with the event to be replicated
on both rows. So, for the above data, you want
A B C D E
1 Date Start End User Color
2 5/28/2015 15:00 21:00 Fred Green
3 6/1/2015 16:25 23:59 Henry Red
4 6/2/2015 0:00 1:00 Henry Red
5 6/4/2015 9:00 13:00 Mary Blue
as a result.
I will assume that you use no more than 23 columns,
so Columns X, Y, and Z are available for use as “helper columns”.
Sheet1 and the new sheet is Sheet2.
The following steps will copy the data from Sheet1 to Sheet2,
splitting rows.Sheet1, row 1, to Sheet2, row 1.=INDEX(Sheet1!A:A, $X2)+$Y2 into Sheet2!A2.=IF($Y2=0, INDEX(Sheet1!B:B, $X2), 0) into Sheet2!B2.=IF(AND($Y2=0,$Z2>0), TIME(23,59,59), INDEX(Sheet1!C:C, $X2)) into Sheet2!C2.=INDEX(Sheet1!D:D, $X2) into Sheet2!D2
and drag/fill to the right to cover all your data
(i.e., to column E, in my example).Sheet1:A2:E2 and paste formats (and column widths, if desired)
onto Sheet2:A2:E2.2 in Sheet2!X2.
This designates the row on Sheet1 that this row (on Sheet2)
will pull data from.0 in Sheet2!Y2.=INDEX(Sheet1!F:F, $X2) into Sheet2!Z2.
This creates a local copy of the “overnight” indicator.Sheet2!A2:Z2 and drag/fill down to row 3.Sheet2!X3 to =IF(AND(Y2=0,Z2>0), X2, X2+1).Sheet2!Y3 to =IF(AND(Y2=0,Z2>0), Y2+1, 0).Sheet2!A3:Z3 and drag/fill down as far as you need
to get all your data.It should look something like this:
A B C D E X Y Z
1 Date Start End User Color
2 5/28/2015 15:00 21:00 Fred Green 2 0 0
3 6/1/2015 16:25 23:59 Henry Red 3 0 1
4 6/2/2015 0:00 1:00 Henry Red 3 1 1
5 6/4/2015 9:00 13:00 Mary Blue 4 0 0
Notes:
Sheet2!Xn specifies
the row on Sheet1 that row n (on Sheet2) will pull data from.Sheet2!Yn is a one-up number
within a Sheet2!Xn value; i.e., within a Sheet1 row; i.e., within an event.
For an overnight event, Y will be 0 for the pre-midnight segment and 1 for the post-midnight segment.
For example, since rows 3 and 4 on Sheet2 pull data fromSheet1 row 3,
we have X3=X4=3, and Y3, Y4 = 0, 1.To make this permanent, you can copy and paste values,
and delete columns X, Y, and Z.
I would create 3 new columns first. Use an excel formula to calculate your new data base on your original columns. Then take the 3 new columns, CUT and PASTE to the bottom of your first 3 columns.
