?
Solved

Macro to record a cell value at specific times

Posted on 2007-08-03
3
Medium Priority
?
234 Views
Last Modified: 2010-04-30
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.
0
Comment
Question by:Cafe_Pombo
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
hiteshgoldeneye earned 900 total points
ID: 19623293
Sub a()
For i=1 to Sheet1.UsedRange.Rows.Count
  if Sheet1.Cells(i,1) = Now() then
           Sheet1.Cells(20,5).Copy ' the cell containing price
          Sheet1.Cells(i,2).PasteSpecial(Paste:=xlValues)
  End if
Next

End Sub
0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 600 total points
ID: 19623341
If you define a macro using the ontime command then you can trigger the process ... using as low a time resolution as makes sense i.e.

Sub mytime()
Application.OnTime earliesttime:=(Now + TimeValue("00:30:00")), procedure:="mytime"

    Worksheets("finalreport").Range("h7") = Now
End Sub

call the routine i.e. in the workbook open event then it recalls itself at some period every 30 mins in this case and instead of the update to H7 you can trigger your time of day checks.  i.e. checking the times in column A and making the price check.

Might give you a clue anyway.

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 19623688
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question