Alternative method fo simple function?

jc0r

Board Regular
Joined
Mar 16, 2009
Messages
121
Hi all, i have the below piece of code that i run every 200 milliseconds. It works fine however i am looking for a better, more efficient way of producing the same result. I use AutoHotKey to get information from Excel and quite often i will get a Callee error, i assume this is because Excel is in Edit mode whilst the routine is in operation?

Any ideas please.

Many thanks

Code:
Sub PriceRefresh()
Worksheets("Data").Range("AJ5:AJ11").Value = Worksheets("Data").Range("AI5:AI11").Value
Worksheets("Data").Range("AI5:AI11").Value = Worksheets("Data").Range("AH5:AH11").Value
Worksheets("Data").Range("AH5:AH11").Value = Worksheets("Data").Range("AG5:AG11").Value
Worksheets("Data").Range("AG5:AG11").Value = Worksheets("Data").Range("AF5:AF11").Value
Worksheets("Data").Range("AF5:AF11").Value = Worksheets("Data").Range("AE5:AE11").Value
Worksheets("Data").Range("AE5:AE11").Value = Worksheets("Data").Range("AD5:AD11").Value
Worksheets("Data").Range("AD5:AD11").Value = Worksheets("Data").Range("AC5:AC11").Value
Worksheets("Data").Range("AC5:AC11").Value = Worksheets("Data").Range("AB5:AB11").Value
Worksheets("Data").Range("AB5:AB11").Value = Worksheets("Data").Range("AA5:AA11").Value
Worksheets("Data").Range("AA5:AB11").Value = Worksheets("Data").Range("F5:F11").Value
Worksheets("Data").Range("AU5:AU11").Value = Worksheets("Data").Range("AT5:AT11").Value
Worksheets("Data").Range("AT5:AT11").Value = Worksheets("Data").Range("AS5:AS11").Value
Worksheets("Data").Range("AS5:AS11").Value = Worksheets("Data").Range("AR5:AR11").Value
Worksheets("Data").Range("AR5:AR11").Value = Worksheets("Data").Range("AQ5:AQ11").Value
Worksheets("Data").Range("AQ5:AQ11").Value = Worksheets("Data").Range("AP5:AP11").Value
Worksheets("Data").Range("AP5:AP11").Value = Worksheets("Data").Range("AO5:AO11").Value
Worksheets("Data").Range("AO5:AO11").Value = Worksheets("Data").Range("AN5:AN11").Value
Worksheets("Data").Range("AN5:AN11").Value = Worksheets("Data").Range("AM5:AM11").Value
Worksheets("Data").Range("AM5:AM11").Value = Worksheets("Data").Range("AL5:AL11").Value
Worksheets("Data").Range("AL5:AL11").Value = Worksheets("Data").Range("H5:H11").Value
End Sub
 
Last edited:

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

Most probably Worksheets("Data") is your active sheet ...so you can delete it

Your copies will gain in speed with Application.Calculation = =xlCalculationManual

Hope this will help
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,562
Office Version
365
Platform
Windows
The code could probably be tidied up/shortened by introducing a loop or two but I don't think that would help with any potential error.

What makes you think the error you mention is caused by Excel being in Edit mode?

If Excel is in Edit mode code shouldn't run.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,712
Office Version
365
Platform
Windows
Code:
Sub PriceRefresh()
Worksheets("Data").Range("AJ5:AJ11").Value = Worksheets("Data").Range("AI5:AI11").Value
Worksheets("Data").Range("AI5:AI11").Value = Worksheets("Data").Range("AH5:AH11").Value
Worksheets("Data").Range("AH5:AH11").Value = Worksheets("Data").Range("AG5:AG11").Value
Worksheets("Data").Range("AG5:AG11").Value = Worksheets("Data").Range("AF5:AF11").Value
Worksheets("Data").Range("AF5:AF11").Value = Worksheets("Data").Range("AE5:AE11").Value
Worksheets("Data").Range("AE5:AE11").Value = Worksheets("Data").Range("AD5:AD11").Value
Worksheets("Data").Range("AD5:AD11").Value = Worksheets("Data").Range("AC5:AC11").Value
Worksheets("Data").Range("AC5:AC11").Value = Worksheets("Data").Range("AB5:AB11").Value
Worksheets("Data").Range("AB5:AB11").Value = Worksheets("Data").Range("AA5:AA11").Value
Worksheets("Data").Range("AA5:A[COLOR="#FF0000"][B][SIZE=+1]B[/SIZE][/B][/COLOR]11").Value = Worksheets("Data").Range("F5:F11").Value
Worksheets("Data").Range("AU5:AU11").Value = Worksheets("Data").Range("AT5:AT11").Value
Worksheets("Data").Range("AT5:AT11").Value = Worksheets("Data").Range("AS5:AS11").Value
Worksheets("Data").Range("AS5:AS11").Value = Worksheets("Data").Range("AR5:AR11").Value
Worksheets("Data").Range("AR5:AR11").Value = Worksheets("Data").Range("AQ5:AQ11").Value
Worksheets("Data").Range("AQ5:AQ11").Value = Worksheets("Data").Range("AP5:AP11").Value
Worksheets("Data").Range("AP5:AP11").Value = Worksheets("Data").Range("AO5:AO11").Value
Worksheets("Data").Range("AO5:AO11").Value = Worksheets("Data").Range("AN5:AN11").Value
Worksheets("Data").Range("AN5:AN11").Value = Worksheets("Data").Range("AM5:AM11").Value
Worksheets("Data").Range("AM5:AM11").Value = Worksheets("Data").Range("AL5:AL11").Value
Worksheets("Data").Range("AL5:AL11").Value = Worksheets("Data").Range("H5:H11").Value
End Sub
I'm assuming that the red B should actually be an A, otherwise you are over-wring AB5:AB11 twice.

This would be a little more efficient.

Rich (BB code):
Sub PriceRefresh_New()
  With Worksheets("Data")
    .Range("AB5:AJ11").Value = .Range("AA5:AI11").Value
    .Range("AA5:AA11").Value = .Range("F5:F11").Value
    .Range("AM5:AU11").Value = .Range("AL5:AT11").Value
    .Range("AL5:AL11").Value = .Range("H5:H11").Value
  End With
End Sub
 

jc0r

Board Regular
Joined
Mar 16, 2009
Messages
121
Wow! Many thanks for your suggestions.

Norie, i am just assuming at this stage that the error is because of my VBA script as there is not anything else contained in the Excel VBA Workbook. On open it just runs that code every 200 milliseconds. I'm not sure if anyone is familiar with Auto Hotkey but the error i recieve is the following:

Code:
Error: 0x80010001 - Call was rejected by callee.

Specifically: Sheets

Line#
---> 026: SelecID := XL.Sheets("Selection".Range("B5").Text

Continue running the script?
Peter_SSs you are indeed correct, that "B" is an error, i will make the change. Although your code is a lot nicer, i am not sure it will achieve what i am after. You see, the function of the code is to create 2 ranges that both contain 10 columns. Working right, each column is the previous columns data then i can draw an average of the data over the last 2 seconds
 
Last edited:

jc0r

Board Regular
Joined
Mar 16, 2009
Messages
121
Can anyone add anything to this please? Either the error im recieving or a better way to write my script? Many thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,099,573
Messages
5,469,472
Members
406,655
Latest member
pwilson06

This Week's Hot Topics

Top