Public SavedTime As Date
Public TimeRng As Range
Public TimeChosen As Range
Public StockSht As Worksheet
Sub StaticStockPrices()
Dim Cel As Range
Set StockSht = Sheets("Stock Price Data")
Set TimeRng = StockSht.Range("StockTimes")
For Each Cel In TimeRng
Debug.Print Format(Cel.Value, "hh:mm")
If Cel.Offset(1, 0) = "" And Int(Now()) + Cel.Value > Now() Then
SavedTime = Int(Now()) + Cel.Value
Set TimeChosen = Cel
Exit For
End If
Next Cel
If SavedTime > 0 Then
Application.OnTime SavedTime, "StockStatic", schedule:=True
End If
End Sub
Sub StockStatic()
Dim OutRng As Range
Dim StockRng As Range
If SavedTime = 0 Then Exit Sub
Set StockRng = StockSht.Range("CurrentStockPriceRng")
Set OutRng = StockSht.Range(TimeChosen.Offset(1, 0), TimeChosen.Offset(StockRng.Rows.Count, 0))
OutRng.Value = StockRng.Value
SavedTime = 0
Call StaticStockPrices
End Sub
Sub StopStockStatic()
On Error Resume Next
Application.OnTime SavedTime, "StockStatic", schedule:=False
On Error GoTo 0
End Sub