05-25-2008, 04:41 AM
I am new to this forum but hope you can help my problem is
I have a DDE Forex pice in cell B2 on worksheet 1, it changes say every second ,I need
to read the price at 30 minute intervals from 08:00 to 21:30 and place it all
on worksheet 2 in the correct order eg time in A AND price in B
one day maybe I can help
Hi jsteve, welcome to the forum! :)
Let's see, what are you going to need for this...
To perform an action periodically, you can use the OnTime method, which allows you to call a procedure following a given schedule.
This procedure needs to copy the value in cell B2 in one worksheet to a different cell in another worksheet. You can do that using the Worksheets, Range, and Cells properties, like so:
Worksheets("Sheet2").Cells(thisRow, thisColumn).Value = Worksheets("Sheet1").Range("B2").Value
Similarly, you can assign the value returned by the Time VBA function to another cell in that worksheet.
Working out the column numbers for those target cells is easy, since they are constants. The row number, however, should vary (if I understood your requirements correctly). You can accomplish this by creating a module level variable, i.e. by putting something like
Private currentRow As Long
into the declaration section of your code module. If you increment this variable each time the procedure is called, you get a new line for each timestamp.
You can find more information about all the keywords in your VB Help.