Capturing The First Change in a Formula

[carl]

I have a spreadsheet that monitors the opening time of some stocks. In the
morning before trading starts it looks like this:

Stock TIME
QQQQ-O 2/15/06
AAPL-O 2/15/06
GOOG-O 2/15/06
CSCO-O 2/15/06
SPY-A 2/15/06

When trading starts, the TIME value records the time of the last trade, thus
the first time will represent the time of the first trade - I am trying to
capture this time value. The problem is that if another trade occurs, the
TIME value updates to the time of that last trade. The TIME value is fed by a
DDE link from a data source.

Is there a way to capture the "first" change in the TIME value ?

Thank you in advance.

 

[Otto Moehrbach]

One way:
Designate some cell somewhere as the place where the first time value is
stored. Make sure this cell is blank when the day starts.
Then use a Worksheet_Change event macro to take the value in the time
cell (the cell that keeps changing) and put it in this designated "First
time" cell, if that cell is empty. That cell won't be empty after the first
time and so will remain static. Post back if you need more. HTH Otto

 

[carl]

Thank you OTTO.

I am not familiar with the using "Worksheet_Change" event. Can guide me
through ?

 

[Otto Moehrbach]

Carl
Let's make the following assumptions:
The time cell that changes continuously is C1
The cell where to put the first time change is F1
Make sure that F1 is blank at the start of the day.
The following macro does what you want

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
If IsEmpty(Range("F1")) Then _
Range("F1").Value = Range("C1").Value
End Sub

Because this macro is a sheet event macro, it must be placed in the module
for that sheet.
To access this module, have that sheet on the screen. Right-click the sheet
tab for that sheet. Select View Code and paste this macro into that module.
Click on the "X" at the top right of the screen to get back to the sheet.
Please post back if you need more. HTH Otto

 

[carl]

Thank you again. Will this work for all of column C or just c1 ? Thanks again ?

 

[Otto Moehrbach]

As written, it copies the value only if C1 changes. If you want this macro
to copy the cell whenever any cell in Column C changes and then change it to
the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
If IsEmpty(Range("F1")) Then _
Range("F1").Value = Target.Value
End Sub

HTH Otto

 

[carl]

Thank you again Otto. Sorry to keep bothering you but am trying to learn.

Here's my original table:

Stock TIME
QQQQ-O 2/14/06
AAPL-O 2/14/06
GOOG-O 2/14/06
CSCO-O 2/14/06
SPY-A 2/14/06

After trading starts each line gets updated with a time. So for each line
(QQQQ-O, AAPL-O, GOOG-O etc) I need to capture the first change of the day.

So the change is at a cell level - not at the worksheet level. Does this
make a difference ?

 

[Otto Moehrbach]

That's how you learn. Keep asking. Otto

Thank you again Otto. Sorry to keep bothering you but am trying to learn.

Here's my original table:

Stock TIME
QQQQ-O 2/14/06
AAPL-O 2/14/06
GOOG-O 2/14/06
CSCO-O 2/14/06
SPY-A 2/14/06

After trading starts each line gets updated with a time. So for each line
(QQQQ-O, AAPL-O, GOOG-O etc) I need to capture the first change of the
day.

So the change is at a cell level - not at the worksheet level. Does this
make a difference ?

 

[carl]

Hi Otto. The code still does not work. Any ideas ?

 

[Otto Moehrbach]

Which code? I gave you 2 of them. Did you put the macro in the sheet
module of the sheet? It won't work if it is not in that module. Send me
direct via email your email address and I'll send you a small file with the
macro in the proper module. My email address is (e-mail address removed).
Remove the "nop" from this address. Otto