Exporting Data into Different Cells at Set Intervals

[jrarmstrong]

Hi,
I have been wrestling with this for a while and am hoping you can help.

I have a piece of trading software that can be connected to a spreadsheet. The software sends price data to cell A1 at various intervals between 0.1 and 0.5 seconds. Each time the price refreshes it overwrites the previous price in the same cell.

I have been trying to export the price data from that cell into new cells every half second (I do not need it more frequently) so that the price is copied into cell B1 , B2 , B3 etc so I end up with a single column containing a list of prices.

Is there a macro that will do this?

Apologies if this is a basic question.

Thanks,

James

[lucas]

Try this:

[VBA]Option Explicit
Public StartTime, DelayInSeconds As Integer
Public gblnStop As Boolean
Function Pause(DelayInSeconds)
'//this declares "Pause" to be a function that can be used in all other subs
StartTime = Timer
Do While Timer < StartTime + DelayInSeconds
DoEvents
Loop
End Function
Sub runTimer()
gblnStop = False
Do
Copytosheet
Pause 0.5
If gblnStop Then Exit Do
Loop
End Sub
Sub stopTimer()
gblnStop = True
End Sub
Sub Copytosheet()
Dim i As Long
i = 0
Sheets("Sheet1").Range("A1").Copy
Do
If Sheets("Sheet1").Range("B" & 1 + i).Value <> "" Then
i = i + 1
End If
Loop Until Sheets("Sheet1").Range("B" & 1 + i).Value = ""
Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet1").Range("B" & 1 + i)
End Sub[/VBA]

[jrarmstrong]

Lucas,

You are a legend. This works perfectly.

Thanks,

James

[lucas]

Just tinkering with some things others have provided.

You can mark your thread solved using the thread tools at the top of the page.

That will keep others from reading the entire thread just to find it's been resolved.