Capturing First Change In A Cell

[carl]

I started this question yesterday.

I monitoring the opening times of stocks. The TIME field is fed by a DDE
link. In the morning before the trading day starts the table looks like this:

Stock TIME
QQQQ-O 12/17/2005
AAPL-O 12/17/2005
GOOG-O 12/17/2005
CSCO-O 12/17/2005

When the stock does it's first trade, I want to record that value. The
problem is that the time is changing continuously - asnd I only want to
record the first change - for example, the table below shows the instant that
the TIME value changes from "12/17/2006" to a time. It is this change that I
am trying to record.


Stock TIME
QQQQ-O 9:31
AAPL-O 9:31
GOOG-O 9:33
CSCO-O 9:34

One of the participants in this forum suggest the use of the follwoing code:

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

I could not get this to work.

I appreciate any suggestions.

Thank you in advance.

 

[vezerid]

I have not seen the original suggestion but IsEmpty tests for
uninitialized variables and not for an empty cell. The following should
(could) work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
If Range("F1") = "" Then Range("F1").Value = Target.Value
End Sub

HTH
Kostis Vezerides

 

[carl]

Thank you. Will this work for each row - not just for F1 ?

 

[Otto Moehrbach]

Carl
As written, that macro works with F1 only. Post back and detail what
you want to happen and for how many cells you want this to happen. HTH
Otto

 

[carl]

Thanks Otto. Here's my question again. I need to record the first change
(only) from the date to the time an all rows. Presently my list has 164
stocks.

I monitoring the opening times of stocks. The TIME field is fed by a DDE
link. In the morning before the trading day starts the table looks like this:

Stock TIME
QQQQ-O 12/17/2005
AAPL-O 12/17/2005
GOOG-O 12/17/2005
CSCO-O 12/17/2005

When the stock does it's first trade, I want to record that value. The
problem is that the time is changing continuously - asnd I only want to
record the first change - for example, the table below shows the instant that
the TIME value changes from "12/17/2006" to a time. It is this change that I
am trying to record.


Stock TIME
QQQQ-O 9:31
AAPL-O 9:31
GOOG-O 9:33
CSCO-O 9:34