History for Realtime measurement

[David_J]

Hello all,

I have a question about copying a value from one cell to another row.

I'm having realtime measurement coming into excel from a flowmeter.
The connection is made with DDE.

All is going well but I have only one cell that gets an update every second or so. Value in the cell is between 0.0000 and 2.0000 and depends on the flow in the flowmeter.
I want to store the information from that realtimecell to the next row to be able to create a graph.

How do I copy (automatic) that cell to a row so I can have some history from that realtime measurement?

Thanks,

David.

[mrice]

I would suggest a macro linked to the worksheet_change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A1").Address Then
Cells(65536, 3).End(xlUp).Offset(1, 0) = Target
End If
End Sub

assuming that the cell that is changing is A1

[David_J]

Thanks mrice,

It works very good.

Now it copy's the A1 cell everytime it changes to B1 then B2 then B...
Is it possible to assing a time to the copy action?
Like every 10 seconds one copy (1 minute would result in B1 > B6)
This would be better to create a graph because the time is known.
A test runs could last for 14 hours with a flow (A1) that changes constant.
The result now is a lot of data...

It would be even better if it was possible to change the time (one copy every 10 sec or one every minute) in another cell (C1?).

Thanks,

David.

[mrice]

Try this

Public RunWhen As Double

Sub Capture()
Cells(1, 1).Copy Destination:=Cells(65536, 2).End(xlUp).Offset(1, 0)
RunWhen = Now + TimeSerial(0, 0, Cells(1, 3))
Application.OnTime RunWhen, "Capture", , True
End Sub

[David_J]

mrice,

Perfect!
It works like a dream.
Thanks!!!!

Gr, David.