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

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:

Sub ZeroAdder()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim dayDiff As Long
Dim loopCounter As Integer
Dim DateToday As Long

Set wks = Worksheets("Sheet Submitted")

For loopCounter = 1 To 3
With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow + 1 Step -1

'*******Select the cell that is LastRow+1 and then add today's date as the last row if it's not already there*********

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 because the date is there
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 = 0
End With

End With
End Select

Next iRow

End With

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?

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

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