Help with logging live data

[sk4520]

I'd like to log live update data continuously,i.e. as the data in the cell changes. The problem I'm facing is that the data comes in at uneven intervals-- ranging from 30 to 50 ticks per second. So I can't really use a timer function. I need to use some function which saves the data as the cell value changes. So, for example, if cell A1 gets updated continuously, cell B1 could save the first value of A1 and then cell B2 could save the second value of cell A1-- and so forth.

Any help would be greatly appreciated.
sk

 

[Joe Was]

Change this Sub to:

Public Sub Recalculate()
'Standard module code.
'This starts the timer!

myReCheck:
On Error GoTo Err

On myStop <> 0 GoTo Err

Calculate
x = 0
For a = 1 to 10000
x = x +1
GoTo myReCheck
Next a
End

Err:
End Sub

Play with the loop "10000" to shorten or increase the time between updates!

This is real bad on the system cycles!

 

[sk4520]

I think I see your issue, you might be linked to Bloomberg or something and are not actually 'changing' the cell. Try this if the previous code I sent hasn't worked:

hold Alt+F11 keys, and paste below code into Sheet1 vba module

Private Sub Worksheet_Calculate()

c = Range("A" & Rows.Count).End(xlUp).Row 'Identifies last row in Column A with data


Range("A" & c + 1).Value = Range("A1").Value
Range("B" & c + 1).Value = Range("B1").Value


End Sub

crimson_b1ade and Joe_Was-- Fantastic!! I just tried this code and it works perfectly-- ending weeks of frustration and pages of code with arrays and loops. Thanks!

One quick question-- how could i get it to end-- say I only wanted a 1000 rows (i.e 1000 ticks).. could I put in a Do-While loop referring to Row.Count?

 

[crimson_b1ade]

you'll need an IF THEN ELSE statement (see below revised code):

Private Sub Worksheet_Calculate()

c = Range("A" & Rows.Count).End(xlUp).Row 'Identifies last row in Column A with data

If c = 1000 Then

Exit Sub

Else

Range("A" & c + 1).Value = Range("A1").Value
Range("B" & c + 1).Value = Range("B1").Value

End If

End Sub

 

[sk4520]

Change this Sub to:

Public Sub Recalculate()
'Standard module code.
'This starts the timer!

myReCheck:
On Error GoTo Err

On myStop <> 0 GoTo Err

Calculate
x = 0
For a = 1 to 10000
x = x +1
GoTo myReCheck
Next a
End

Err:
End Sub

Play with the loop "10000" to shorten or increase the time between updates!

This is real bad on the system cycles!

you'll need an IF THEN ELSE statement (see below revised code):

Private Sub Worksheet_Calculate()

c = Range("A" & Rows.Count).End(xlUp).Row 'Identifies last row in Column A with data

If c = 1000 Then

Exit Sub

Else

Range("A" & c + 1).Value = Range("A1").Value
Range("B" & c + 1).Value = Range("B1").Value

End If

End Sub

Great!! Thanks very much. Works perfectly.

 

[sk4520]

Help with saving logged live data

Hi!

Thanks to your help on my previous posting, I'm now able to successfully log live data continuously. Now I'm trying to save a chunk of the logged data to a csv file in a certain location with a certain date when certain conditions are met-- for example when row 30,000 is reached, I'd like to save all the values in A1: B30000 in a csv file with the last time stamp. Then I'd like all the rows in the log to be deleted and the logging process to be started over. How could I do this well?


Could I also save the values in rows in a csv file when a time condition is met? Since I'm logging the values and next to it the time stamp, could I save the data in a csv file everytime say the time 12:00 am is reached?

Thanks very much.
sk4520