Maybe:
Option Explicit
Sub testme()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim dayDiff As Long
Dim mySheetNames As Variant
Dim iCtr As Long
mySheetNames = Array("sheet submitted", "Sheet Fixed", "sheet closed")
For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
Set wks = Worksheets(mySheetNames(iCtr))
With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If CLng(.Cells(LastRow, "A").Value) < CLng(Date) Then
With .Cells(LastRow + 1, "A")
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
LastRow = LastRow + 1
End If
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
Next iCtr
End Sub
Your method with the loopcounter stuff was interesting.
I used an alternate version--just to be different.
Erikka T wrote:
One more request. It is working great except for one thing: I would like to add today's date to the LastRow so that my date range includes "Today". I have tried doing this with various methods, but can't seem to get the correct cell selected in each sheet. Here is my current code:
Select Case loopCounter
Case Is = 1: Set wks = Worksheets("Sheet Fixed")
Case Is = 2: Set wks = Worksheets("Sheet Closed")
End Select
Next
End Sub 'AddZeros
-----------------------------------------
Thanks for any help you can offer!
Erikka
"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