View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Vladi Vladi is offline
external usenet poster
 
Posts: 1
Default help with a special timer

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