Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query returned more data than will fit on a worksheet | Excel Worksheet Functions | |||
average daily counts to weekly counts | Excel Discussion (Misc queries) | |||
Query returned more data than will fit on a worksheet | Excel Discussion (Misc queries) | |||
Pivot Table counts instead of data | Excel Worksheet Functions | |||
Would like to add 0 counts to worksheet after data is returned fro | Excel Programming |