MrExcel Publishing
Your One Stop for Excel Tips & Solutions

rolling date


Posted by ken on June 28, 2000 1:30 AM

Is there a macro that will put todays date in the last row of the first column each day

explanation
i want todays date in cell a4
tommorows in a5
but i don't want the date to show up till it gets here.
such as today is the 28th so it should have everyday before that but tomorrow won't show up till tomorrow then a formula will go into other cells in that row.


also is there a way to search though those dates for a particular date and input data into that row


Posted by Ryan on June 28, 0100 8:38 AM

Ken,
Here is some code that will take care of what you want. The first code (WorkBook_Open) goes in the ThisWorkbook module, and will execute whenever the workbook is open. If the last date entered is not todays date, then it will enter todays date in the next column. The next code (FindDate) goes in a normal module and will take you to the date that you enter in an input box. You can modify the code how ever you need it. If you need somemore help let me know. Hope this helps. Let me know!
Ryan
' This assumes the dates are in column A

Private Sub Workbook_Open()
Dim LastRow As Integer

LastRow = Range("A65536").End(xlUp).Row
If Cells(LastRow, 1).Value <> Date Then Cells(LastRow + 1, 1).Value = Date

End Sub


' This brings up an input box to enter a date into and then takes you to that date.


Sub FindDate()
Dim FindDate As Date
Dim x As Integer

On Error GoTo Handler

FindDate = InputBox("Enter date", "Date")

x = 1


Do While Cells(x, 1).Value <> FindDate
x = x + 1
CellRow = x
Loop
Rows(CellRow).Select
Exit Sub

Handler:
MsgBox "Unable to find date", vbCritical, "Error"
End Sub

Posted by Ryan on June 28, 0100 8:40 AM

Mistake

Ken,
I said that the code will put todays date in the next column, I meant the next row, like you wanted it. Sorry about that.

Ryan

Posted by Ryan on June 29, 0100 8:43 AM

Re: Mistake

Ken,
Here you go, works like a charm, let me know!
Ryan

Private Sub Workbook_Open()
Dim LastRow As Integer
Dim Done As Boolean

Done = False
Do While Done = False
LastRow = Range("A65536").End(xlUp).Row
If Cells(LastRow, 1).Value = "" Then
Cells(LastRow, 1).Value = Date
Exit Sub
End If
If Cells(LastRow,1).Value > Date Then Exit Sub
If Cells(LastRow, 1).Value = Date Then Exit Sub
If Cells(LastRow, 1).Value = Date - 1 Then
Cells(LastRow + 1, 1).Value = Date
Done = True
Else
Cells(LastRow + 1, 1).Value = Cells(LastRow, 1).Value + 1
Done = False
End If
Loop
End Sub

Posted by Ryan on June 30, 0100 12:50 PM

Re: Perfect

Let me know what you need!

Ryan

Posted by ken on June 28, 0100 9:25 PM

Re: Mistake

What about if it wan not open for a few days though and i want those dates too
and i want only certain sheets to do this.

Posted by ken on June 29, 0100 11:23 PM

Perfect

That was nice. You want to make it skip weekends and hlidays too? and add a few formula's to the other rows. And only do it to certain sheets? Or do I have to do some of the work myself?

Thanks for the help. It works great.