Would like to add 0 counts to worksheet after data is returned fro
 

Sometimes, it's easier starting at the bottom and working your way up.

I guessed that you had 4 columns (date, time, something, and qty).

Make sure the data is sorted first.

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim dayDiff As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
dayDiff = .Cells(iRow, "A").Value - .Cells(iRow - 1, "A").Value
Select Case dayDiff
Case Is <= 0: MsgBox "Not sorted--or duplicated dates!"
Case Is = 1: 'do nothing
Case Else
.Rows(iRow).Resize(dayDiff - 1).Insert
With .Cells(iRow, "A").Resize(dayDiff - 1)
.FormulaR1C1 = "=r[-1]c + 1"
.Value = .Value
.NumberFormat = "mm/dd/yyyy"
With .Offset(0, 1)
.Value = TimeSerial(17, 0, 0)
.NumberFormat = "hh:mm"
End With
With .Offset(0, 3)
.Value = 0
End With
End With
End Select
Next iRow
End With
End Sub



Erikka T wrote:

I am attaching to a database and grabbing records back that are sorted chronologically. I then use the results to make a chart so that I can publish the trends. Here is what I currently get:
5/12/2002 17:00 4 Submitted
5/13/2002 17:00 4 Submitted
5/18/2002 17:00 1 Submitted
5/19/2002 17:00 1 Submitted

The problem is that some days there are no new records submitted. I would like 0 place-holders to be inserted so that the charts will reflect this. This is what I want
the sheet to look like:

5/12/2002 17:00 4 Submitted
5/13/2002 17:00 4 Submitted
5/14/2002 17:00 0
5/15/2002 17:00 0
5/16/2002 17:00 0
5/17/2002 17:00 0
5/18/2002 17:00 1 Submitted
5/19/2002 17:00 1 Submitted

How can I write a loop that will go and look for the date, then compare it to the next row below it and see if the date is the next day. If yes, skip it, if no, insert the missing date and stuff a 0 into the next cell?

--

Dave Peterson