how to keep previous data in cell?

[evalweb]

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...

[rylo]

Hi

Try something like

Please Login or Register  to view this content.

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

[evalweb]

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

[rylo]

Hi

OK try

Please Login or Register  to view this content.

rylo

[evalweb]

Thanks again for helping me...
I have tried your code in order to have the most current number in the upper cells and the older ones are pushed down. it does the job but everytime it also insert a blank line...

ok here is the code that I arranged according to what I want but wihout the last number being displayed on the top (in line 51):

Sub STRENGTH()

Sheets("Matrix").Select
Range("B51:F51").Select

Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = Range("E17").Value
Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = Range("D22").Value
Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Value = Range("F22").Value
Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Value = Range("H22").Value
Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Value = Range("J22").Value

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

End Sub


So what would you change in order to have the latest number to be copy in line 51 so that it pushes the oldest one down by one line...

I have tried

Range("B51:F51").Insert Shift:=xlDown

it does the job but it insert a blank line in 51 everytime news numbers are copied...

what should I modify...


Hope you understand what i need...

Thanks.

[rylo]

Hi

Try this.

Please Login or Register  to view this content.

rylo

[evalweb]

Ok thanks again.
since you are so accurate I would ask you one last question

I know how to have the macro STRENGTH to start by clicking on a button that I inserted in the worksheet but I cannot find how to pause it or stop it by using a stop button...

What code could I add in order to be able to click on an object such as a button and be able to stop the macro STRENGTH from running...

So basically what would be a macro to stop another macro fro running, to actually End the Strength macro...

So let say I have:

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

Sub CloseStrength()

............

End Sub

What code could i insert where the dots are in order to end STRENGTH from running

Thank you very much...

[rylo]

Hi

To do something like that, you need to have the actual time that the sub is scheduled to run, then stop it at that time.

Have a look at
http://www.cpearson.com/excel/OnTime.aspx

If you want something more clunky, then the CTRL BREAK when it is running and then go end, or you can manually modify the code to comment out the next ontime line, so when it runs it doesn't call it again.


rylo

[mudraker]

evalweb

Please read forum rules & wrap your VBA code - see links below for rules

If you do not understand the VBA code wrap instructions have a look at my last reply in this thread
http://www.excelforum.com/showthread.php?t=583950