Auto record cell data

**gally** · 11-01-2012, 06:03 PM

Anybody able to help me with this issue I am having. I have posted some code below which I am using to automatically record a cells data and stamp data in another cell.

Sub ValueStore()
Dim dTime As Date
   Worksheets("Data Capture").Range("A" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Worksheets("Match Odds").Range("H9").Value
  
    Call StartTimer
End Sub


Sub StartTimer()
    dTime = Now + TimeValue("00:10:00")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

The time is set to record the data every 10 minutes but this can be changed via the clock on the Sub StartTimer. I have a cell on the excel sheet on which I use this code which is linked to an external source and has a 24hr clock countdown to an event starting.

What I am trying to do is when the cell with the countdown reaches 1 hour untill the event start it automatically changes from recording the data every 10 minutes to every 1 second. Thank you taking the time to look at this for me...

Gally

Mod's Note: code tags added for you this time

**AlphaFrog** · 11-01-2012, 06:36 PM

Try something like this. Change the sheet name and cell reference to the cell that has your countdown clock.

Sub StartTimer()
    If Sheets("Sheet1").Range("A1").Value <= TimeValue("01:00:00") Then
        dTime = Now + TimeValue("00:00:01")
    Else
        dTime = Now + TimeValue("00:10:00")
    End If
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

**FDibbins** · 11-01-2012, 06:37 PM

gally...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here