i have tried searching , googling solutions no avail. apologise if there solutions out there had missed.
(my excel database) - before running macro
start date start time number finish date finish time 14-jul-15 22:00 1 16-jul-15 06:00 15-jul-15 22:00 1 17-jul-15 06:00 15-jul-15 22:00 1 16-jul-15 06:00 (my excel database) - after running macro
start date start time number finish date finish time 14-jul-15 22:00 1 16-jul-15 06:00 14-jul-15 22:00 1 15-jul-15 06:00 15-jul-15 22:00 1 16-jul-15 06:00 15-jul-15 22:00 1 17-jul-15 06:00 15-jul-15 22:00 1 16-jul-15 06:00 16-jul-15 22:00 1 17-jul-15 06:00 15-jul-15 22:00 1 16-jul-15 06:00 what want macro is, check if difference of "start date" , "finish date" more 2. if difference between both 1 or 0, moves on next row of data check. however, if more 0 or 1, adds row under particular row. copies similar content modifying date.
for example,
14-jul-15 22:00 1 16-jul-15 06:00 after macro, adds under original row.
14-jul-15 22:00 1 15-jul-15 06:00 15-jul-15 22:00 1 16-jul-15 06:00 sorry if question unclear, please assist me in creating macro.
when inserting rows in loop, start @ bottom , work or risk losing step sequence new rows being inserted. stepping upwards newly inserted rows assume row numbers out of scope loop.
sub stackoverflow() dim rw long, long activesheet rw = .cells(rows.count, 1).end(xlup).row 2 step -1 if (cdate(.cells(rw, 4).value2) - cdate(.cells(rw, 1).value2)) > 1 = 1 (cdate(.cells(rw, 4).value2) - cdate(.cells(rw, 1).value2)) .cells(rw, 1).resize(1, 5) .copy .insert shift:=xldown ', copyorigin:=.cells end .cells(rw + 1, 4) = .cells(rw + 1, 4).value2 - (i - 1) .cells(rw + 1, 1) = .cells(rw + 1, 4).value2 - 1 next end if next rw end application.cutcopymode = false end sub if sample data accurately represented actual data above should expand date ranges single day separation each row.
Comments
Post a Comment