Hello,
The Sub Procedure below works with real time data from an online server via a Excel 2007 Add In. Is it possible to achieve the same outcome without having to select anything ?
Being a novice at VBA I have been scratching my head as how to solve this. This Procedure is executed every ten seconds via a timer and have found it necessary to include the underlined statements to position the active cell in preparation to call the next Procedure. The idea being that I would like to work with other worksheets to draw charts etc with the VBA programme running and not taken back to the “Long” worksheet every ten seconds.
Any help will much appreciated.
Rob.
The Sub Procedure below works with real time data from an online server via a Excel 2007 Add In. Is it possible to achieve the same outcome without having to select anything ?
Being a novice at VBA I have been scratching my head as how to solve this. This Procedure is executed every ten seconds via a timer and have found it necessary to include the underlined statements to position the active cell in preparation to call the next Procedure. The idea being that I would like to work with other worksheets to draw charts etc with the VBA programme running and not taken back to the “Long” worksheet every ten seconds.
Code:
Sub MarketInterface()
Dim rtd As Range
Dim i As Integer
Application.ScreenUpdating = False
'If the Combined Indicator is greater than zero and the traded value is greater than $30,000 then copy the RTD (Real Time Data) to the User Interface WS
Sheets("Market Interface").Select
Range("aj10").Select
For i = 1 To 1581
Set rtd = Selection.Offset(, -30).Resize(, 15)
If Selection.Value > 0 And Selection.Offset(0, -31) = 0 And Selection.Offset(0, -33).Value > Selection.Offset(0, -32).Value Then _
rtd.Copy Sheets("Long").Range("f:f").Cells(Rows.Count).End(xlUp).Offset(1, 0)
Selection.Offset(1, 0).Select
Next i
'(As above)then add the value 1 to column E of the selected row to by-pass this stock on the next loop.
Range("aj10").Select
For i = 1 To 1581
If Selection.Value > 0 And Selection.Offset(0, -31) = 0 And Selection.Offset(0, -33).Value > Selection.Offset(0, -32).Value Then _
Selection.Offset(0, -31).Value = 1
Selection.Offset(1, 0).Select
Next i
[U]Sheets("Long").Select
[/U] [U]Range("f:f").Cells(Rows.Count).End(xlUp).Offset(1, 0).Select[/U]
Call Long_rec
End Sub
Sub Long_rec()
Application.ScreenUpdating = False
'places the time next to the stock symbol at the time when the stock symbol was selected
Sheets("Long").Select
Range("e:e").Cells(Rows.Count).End(xlUp).Offset(1, 0).Select
Do Until IsEmpty(Selection.Offset(0, 1).Value)
If Not IsEmpty(Selection.Offset(0, 1)) Then Selection.Value = Now
Selection.Offset(1, 0).Select
Loop
Sheets("Long").Select
Range("a:a").Cells(Rows.Count).End(xlUp).Offset(1, 0).Select
'copies the formulas to the left of the Time of Selection (column L) column
Do Until IsEmpty(Selection.Offset(0, 4).Value)
If Not IsEmpty(Selection.Offset(0, 4)) Then Range("a9:d9").Copy Range("a:a").Cells(Rows.Count).End(xlUp).Offset(1, 0)
Selection.Offset(1, 0).Select
Loop
Call Copy_values
End Sub
Rob.