help with a special timer

  • Thread starter Thread starter Vladi
  • Start date Start date
V

Vladi

I'm trying to build a timer which would place a time stamp sharply each
minute in a column like this:

10:30:00 AM
10:31:00 AM
10:32:00 AM

Can anyone help me debug my code as it doesn't want to move down below the
first cell?

Public Sub timer()
Dim Lag
Lag = 60 - Second(Now())
If Second(Now()) = 0 Then
Worksheets("Sheet1").Cells(2, 3).Value = Format(Now(), "hh:mm:ss AM/PM")
End If
If Second(Now()) > 0 Then
Application.OnTime Now() + TimeSerial(0, 0, Lag), "Rounder"
End If
End Sub

Public Sub Rounder()
Worksheets("Sheet1").Cells(2, 3).Value = Format(Now(), "hh:mm:ss AM/PM")
Application.OnTime Now() + TimeSerial(0, 0, Lag), "My_time"
End Sub

Sub My_time()
Dim RunWhen
Dim TimeRunner
TimeRunner = Range("C2").Value
RunWhen = TimeRunner + TimeSerial(0, 1, 0)
Application.OnTime RunWhen, "Update"
End Sub

Sub Update()
Dim Destination As Range
Set Destination = _
Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
Destination.Value = RunWhen
End Sub

***********************
Thanks,
Vladi
 
Set a variable, say Dest, as range.
Set Dest = Range("C2")
Then place the time in the Dest cell.
Then after the line of code that places the time in Dest, set Dest =
Dest.Offset(1)
HTH Otto
 
Back
Top