Alternative to selecting ?

shumba

Board Regular
Joined
Oct 5, 2010
Messages
168
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.

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
Any help will much appreciated.

Rob.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
When you record a macro, the macro records your selections... but in fact macros rarely need selections to be happened.

Wherever you have "Selection", replace it with the cell reference itself. For example:

Code:
Sheets("Market Interface").Select
Range("aj10").Select
Selection.Value

could be replaced with

Code:
Sheets("Market Interface").Range("aj10").Value

Or even better, if you repeatedly use that particular reference, put this at the top:

Code:
Dim rngThatCell as Range
Set rngThatCell = Sheets("Market Interface").Range("aj10")

Then refer to it using, for example:

Code:
rngThatCell.Value
 
Upvote 0
By the way - and this is unrelated - I'm a bit perplexed as to why you have a loop that does the same thing to the same cell 1581 times. The "i" increments which each iteration of the loop... but nothing inside the loop refers to the i! Perhaps I'm missing something, but this could be what's slowing your code.
 
Upvote 0
Forgot to mention: you can call Sub Long_rec() without a selection.

Instead of this:

Code:
...

Sheets("Long").Select
Range("f:f").Cells(Rows.Count).End(xlUp).Offset(1, 0).Select
      
Call Long_rec
End Sub
  
Sub Long_rec()

...

with this:

Code:
...

Call Long_rec(Sheets("Long").Range("f:f").Cells(Rows.Count).End(xlUp).Offset(1, 0))
End Sub
  
Sub Long_rec(rngSomeOtherCell as Range)

...

Then, inside Sub Long_rec, replace your "Selection" stuff with rngSomeOtherCell.
 
Upvote 0

Forum statistics

Threads
1,223,391
Messages
6,171,813
Members
452,426
Latest member
cmachael

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top