capturing a cell value at a specific time of day

T

Tom Donino

In answering my own question from yesterday, my
programmer wrote this code which will capture the value
of cell which is updating realtime, save for that day's
value and copy the formula to the next cell to
dynamically update for the next day. (for use with data
such as stock prices that are realtime)

ie sMacro recorded 1/13/2004 by jenko
'
' Keyboard Shortcut: Ctrl+j
'
DoAfterMarketUpdate

End Sub

Private Sub DoAfterMarketUpdate()
Call DoUpdateSingleCol(ThisWorkbook.Sheets("2004").Range
("SMHPriceCol"), _
ThisWorkbook.Sheets("2004").Range
("SMHValue").Address)
Call DoUpdateSingleCol(ThisWorkbook.Sheets("2004").Range
("SPYPriceCol"), _
ThisWorkbook.Sheets("2004").Range
("SPYValue").Address)

End Sub


Private Sub DoUpdateSingleCol(TargCol As Range,
ValueAddress)

Dim TargRange As Range
Dim TargCell As Range
'TargRange gets set to the top of the Date column
Set TargRange = ThisWorkbook.Sheets("2004").Range
("DateHeader").Offset(1, 0)
'Keep going down the Date column until you get to either
1) a blank cell or 2) a date that's >= today
While (TargRange.Value < Date) And (TargRange.Value
<> "") ' go to 1st non-blank cell that's >= current date
in Date column
Set TargRange = TargRange.Offset(1, 0)
Wend
' if the cell we're now looking at has a bigger date than
today, today must not be in the column, so just leave
If (TargRange.Value > Date) Or (TargRange.Value = "")
Then Exit Sub
' if we got here, we're good to go
'
'
' set TargCell to be the intersection of the Price Column
(SMH or SPY, according to subroutine argument)
' and the row that today's date is in
Set TargCell = Application.Intersect(TargCol,
TargRange.EntireRow)

' Now, set the value in TargCell to be the value in our
(SMH or SPY) price cell
TargCell.Value = ThisWorkbook.Sheets("2004").Range
(ValueAddress).Value
' Now, go down one cell
Set TargCell = TargCell.Offset(1, 0)
' and put the FORMULA from our price cell into TargCell
TargCell.Formula = ThisWorkbook.Sheets("2004").Range
(ValueAddress).Formula

End Sub



tock prices realtime)
 
Top