2760

Timeline - loop through all dates between first and last given and add date to column if not found

Question:

what I have in columns A, B, C:

Date Hours Name <br /> 01/03/2016 8,0 John <br /> 02/03/2016 8,0 John <br /> 08/03/2016 7,5 John <br /> 08/03/2016 2,0 Charles <br /> 08/03/2016 2,0 William <br /> 10/03/2016 3,5 Charles <br /> 11/03/2016 3,7 Charles <br /> 14/03/2016 2,2 Charles <br /> 15/03/2016 8,0 John <br /> 16/03/2016 8,0 John <br />

what I want in column A, B, C in another sheet:

Date Hours Name <br /> 01/03/2016 8,0 John <br /> 02/03/2016 8,0 John <br /> 03/03/2016 0,0 - <br /> 04/03/2016 0,0 - <br /> 05/03/2016 0,0 - <br /> 06/03/2016 0,0 - <br /> 07/03/2016 0,0 - <br /> 08/03/2016 7,5 John <br /> 08/03/2016 2,0 Charles <br /> 08/03/2016 2,0 William <br /> 09/03/2016 0,0 - <br /> 10/03/2016 3,5 Charles <br /> 11/03/2016 3,7 Charles <br /> 12/03/2016 0,0 - <br /> 13/03/2016 0,0 - <br /> 14/03/2016 2,2 Charles <br /> 15/03/2016 8,0 John <br /> 16/03/2016 8,0 John <br />

It has to work with any given dates, hours and names!

Please help I really need this!

Sub proj0() Dim lRow As Long Dim Data1, Data2 As Date Dim C1, C2 As String Folha11.Select Columns("a:c").Select Selection.Copy Folha13.Select Range("A1").Select ActiveSheet.Paste Cells.Select Selection.Sort _ Key1:=Range("a2"), Order1:=xlAscending, _ key2:=Range("c2"), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal lRow = 2 Do While (Cells(lRow, 1) <> "") C1 = Cells(lRow, "c") C2 = Cells(lRow + 1, "c") Data1 = Cells(lRow, "a") Data2 = Cells(lRow + 1, "a") If (Data2 - Data1 > 1) Then ActiveCell.EntireRow.Insert shift:=xlDown Cells(lRow + 1, "a").Value = Data1 + 1 Cells(lRow + 1, "b").Value = 0 Cells(lRow + 1, "c").Value = "-" Else lRow = lRow + 1 End If Loop Range("a:c").Columns.AutoFit Folha13.Select

I believe i'm close but cant figure the insert part

Answer1:

This does what you suggest:

Sub timeline() Dim i As Long Dim ws As Worksheet Dim ts As Worksheet Set ws = Sheets("Sheet15") 'Change to your Output Sheet Set ts = Sheets("Sheet14") 'Change to your data sheet With ws i = ts.Range("A" & ts.Rows.Count).End(xlUp).Row ts.Range("A1:C" & i).Copy .Range("A1") .Range("A1:C" & i).Sort Key1:=.Range("A2"), Order1:=xlAscending, _ key2:=.Range("C2"), Order2:=xlAscending, _ Header:=xlYes Do Until i = 2 If .Cells(i, 1).Value2 = .Cells(i - 1, 1).Value2 Or .Cells(i, 1).Value2 = .Cells(i - 1, 1).Value2 + 1 Then i = i - 1 Else .Rows(i).Insert .Cells(i, 1).Value = .Cells(i + 1, 1).Value2 - 1 .Cells(i, 2).Value = 0# .Cells(i, 3).Value = "--" End If Loop End With End Sub

Sheet14 Before:

<a href="https://i.stack.imgur.com/yM5TB.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/yM5TB.png" data-original="https://i.stack.imgur.com/yM5TB.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

Sheet15 After:

<a href="https://i.stack.imgur.com/u1DEl.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/u1DEl.png" data-original="https://i.stack.imgur.com/u1DEl.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

Recommend

  • Formatting with Charts
  • Export data from Excel to Access using VBA
  • vba paste values and keep source formatting?
  • Yahoo finance historical stock price power query returns 301 response
  • How to calculate total Fridays between two dates in excel on weekly, biweekly and monthly basis?
  • add a publickey to server for SCP [closed]
  • Simplify where clause with repeated associated type restrictions
  • Excel distinct count with conditions
  • Emacs lua-mode issue: (void-function interactively-called-p)
  • Want to understand iframe breakout code
  • Guava how to copy all files from one directory to another
  • xtable - background colour of added rows
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • cell spacing in div table
  • several dataProvider per one Test in TestNG
  • Most efficient way to move table rows from one table to another
  • Flash radiobutton: how do I get the selected radiobutton?
  • Primefaces :radioButton inside a ui:repeat
  • How can I restyle a word when rendering a pdf with pdf.js?
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • std::remove_copy_if_ valgrind bytes in block are possibly lost in loss record
  • Run multiple queries from 1 SQL file showing result in multiple tables
  • Conversion from string “a” to type 'Boolean' is not valid
  • Breaking out column by groups in Pandas
  • Unable to get column index with table.getColumn method using custom table Model
  • Calling Worksheet functions from vba in foreign language versions of Excel
  • Excel's Macro-Recorder usage
  • Xcode 4 NSLog Macro link in Xcode 3
  • Functions in global context
  • Excel - Autoshape get it's name from cell (value)
  • How to handle AllServersUnavailable Exception
  • vba code to select only visible cells in specific column except heading
  • VBA Convert delimiter text file to Excel
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • XCode can't find symbols for a specific iOS library/framework project
  • Data Validation Drop Down Box Arrow Disappearing
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • Reading document lines to the user (python)