Link to home
Start Free TrialLog in
Avatar of Cafe_Pombo
Cafe_Pombo

asked on

Macro to record a cell value at specific times

I'm looking to write a macro that records a cell's value (call this price) when a cell displaying the current time is matched against a list of times in column A.

I started by putting a list of times in column A and thought if I could write a macro that said "If cell "time" is found in column A then copy cell "price" and paste special -> values to the same row in column B, but I'm really struggling to come up with any meaningful code.

If anyone could provide any assistance, it would be very much appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Hitesh Manglani
Hitesh Manglani
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image
i.e. in the workbook module place the following:

Private Sub Workbook_Open()
'Start the timer when the workbook is opened
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 'Terminate activity on close
    StopTimer
End Sub

and in a code module place:

Option Explicit

Public RunTime As Date
Public TimerRunning As Boolean
Sub TimerAction()

'Set variable that says when procedure should run again
' Before action ensure 10s between calls
' After action ensure 10s until next call
    RunTime = Now + TimeValue("00:00:10")

' Identify your required actions:
    Worksheets("finalreport").Range("h7") = Worksheets("finalreport").Range("h7") + 1

'Schedule procedure to run again
    Application.OnTime _
        EarliestTime:=RunTime, _
        Procedure:="TimerAction", _
        Schedule:=True

End Sub
Sub StartTimer()
'Run the procedure every 10 seconds
    RunTime = Now + TimeValue("00:00:10")
 
    If Not TimerRunning Then
        TimerRunning = True
        'Schedule procedure to run
        Application.OnTime _
            EarliestTime:=RunTime, _
            Procedure:="TimerAction", _
            Schedule:=True
    End If

End Sub
Sub StopTimer()
'Run the procedure every 10 seconds
'    RunTime = Now + TimeValue("00:00:10")
 
    If TimerRunning Then
    'Stop the Timer procedure
        Application.OnTime _
            EarliestTime:=RunTime, _
            Procedure:="TimerAction", _
            Schedule:=False
        TimerRunning = False
    End If
   
End Sub

Chris