Select method of range class failed error

**sa02000** · 04-06-2012, 07:02 AM

When I try to run one of my macro, at the following line:
Sheets("historical data").Range("O107").Select

I get an error popup that says: Run time error 1004. select method of range class failed.

Can someone please let me know what am I doing wrong? All I am trying to do is select cell O107 in sheet named "historical data" (I have checked that sheet name is correct).

Thank you in advance
Jay

**tom1977** · 04-06-2012, 07:05 AM

Hi maybe the "historical data" sheet is not the active one during runnig the code (You can use select method only on active sheet) and other thing is that You do not have to select object to change this

**Firefly2012** · 04-06-2012, 07:07 AM

Hi Jay

To select a cell, the cell that is selected must be on the Activesheet. So to ensure it works you could use:

Please Login or Register  to view this content.

However, you rarely need to select anything to work with it - if you explain what you want to do we can probably suggest alternative (and better) ways to achieve it.

**sa02000** · 04-06-2012, 07:30 AM

Thank you very much for quick responses. I am doing just some basic copy paste after running an API for one of the online brokers. Here is the code that I have so far below. I have also attached the file (original of which was provided by Interactive Brokers) but this file won't work without a valid connection to broker. I know I am not great at this programming so any suggestions to reduce this code and looping will be helpful otherwise I am sure I will figure it out in few years time

Please Login or Register  to view this content.

**davesexcel** · 04-06-2012, 08:41 AM

I took 1 section of your provided code.

Please Login or Register  to view this content.

Cleaned up it becomes and removes 7 select actions.
Here are some copy and paste code examples

Please Login or Register  to view this content.

**sa02000** · 04-06-2012, 10:40 AM

Thank you Dave. This sure looks lot more concise and reusable.

When I run this, I get the following error for both of these two lines in the code

Please Login or Register  to view this content.

Run-time Error '91'
Object variable or With block variable not set.

Any idea what might be causing this?

Thank you
Jay

**davesexcel** · 04-06-2012, 10:50 AM

Is the sheet you want to copy from empty? I opened your workbook, ran my bit of the code and got the same error. Once I placed data on the sheet, the code worked fine.

Maybe edit your copy and paste line in case yo have more then 65536 rows,

Please Login or Register  to view this content.

**sa02000** · 04-07-2012, 10:13 AM

You are right. There was no data, once I made sure I was getting data from their server it works perfectly.
Thank you very much. Added to your reputation

Jay