how to keep previous data in cell?

Hello

I have a cell A1 that is updated every 5 seconds from data I receive from a DDE server (those data are just numbers between 0 and 10)

What I need is to store every minute the number from A1 in order to keep historical data of those numbers.

So let say at 8:00, A1 number is copied in C2 cell and kept, at 8:01 A1 number is copied in C3 cell and kept, at 8:02 A1 number is copied in C4 cell and kept...
so basically I need 2 or 3 column
column A is just for A1 values that changes every 5 seconds or so
column B is the time with an increment of 1mn
column C is the value of A1 at the time in B like a snapshot at a specific time....

are there any formula or command or macro to do that, and if yes, how?

Hope you can help me...

Hi

Try something like

Sub bbb()
  Cells(Rows.count, "C").End(xlUp).offset(1, 0).Value = Range("A1").Value
  Application.OnTime Now() + TimeValue("00:01:00"), "bbb"
End Sub

You will have to fire it off, but once it is initiated, it will rerun every minute and output the number to the next available cell in column C.

To fire it off, you could either do it manually, or create an auto_open procedure to kick off when the workbook is opened.

HTH

rylo

Thank you..

It works very well...

just one more question...
what would need to be changed if i want the last number to be copied from A1 to be always in C and the oldest are going down the column...
Now it's the opposite... the new number is added at the bottom... I need it to be added at the top of teh column and it pushes the oldest one down...

I hope you understand what I am looking for...

thank you

Hi

OK try

Sub bbb()
  Range("A1").Copy
  Range("C1").Insert Shift:=xlDown
  Application.CutCopyMode = False
  Application.OnTime Now() + TimeValue("00:01:00"), "bbb"
End Sub

rylo

Hi

Try this.

Sub STRENGTH()

Sheets("Matrix").Select
Range("B51:F51").Insert shift:=xlDown



Range("B51").Value = Range("E17").Value
Range("C51").Value = Range("D22").Value
Range("D51").Value = Range("F22").Value
Range("E51").Value = Range("H22").Value
Range("F51").Value = Range("J22").Value


Application.OnTime Now() + TimeValue("00:01:00"), "STRENGTH"

End Sub

rylo