Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Select Method or Range Class failed

  Asked By: Grant    Date: Jan 09    Category: MS Office    Views: 1688
  

I am getting error 1004 "Select
Method or Range Class failed" when I attempt to select a cell on a
worksheet that I move to as part of a code block I have written.
This code did work fine before, but I had it in a test file, and am
now working on adding it to the real file. I have made some minor
changes to the ranges it is selecting, but did not alter these lines
at all! The worksheet is visible and the workbook will switch to the
appropriate sheet, but I can't seem to select a cell on it:

Worksheets("Maria").Select
Cells(30, 10).Select

The above code generates the error. What makes it so maddening is
that I use this same code structure earlier in the same module and
experience no issues with it there - only here does the error occur:

Worksheets("Matrix").Select
Cells(10, 1).Select

I just don't know what to do to make this work. Any help would be
greatly appreciated!

Share: 

 

11 Answers Found

 
Answer #1    Answered By: Joseph Evans     Answered On: Jan 09

I've had something similar happen.
I have no idea if my 'solution' actually fixed it or if it
was just a coincidence.
But it "seemed" that even though I "selected" the worksheet.
the "Active" sheet remained the same.
i added the line: worksheets("Maria").activate (for example)
after the Worksheets("Maria").select statement, and it seems to have fixed it!

Like I said, it may not even be the problem...
give it a shot..

 
Answer #2    Answered By: Janis Hart     Answered On: Jan 09

I gave it a shot, but no dice. What's weird is that it switches to
the worksheet, and I can move the focus around on the worksheet if I
use "Activecell.Offset(x,y).Select", but if i try to select  a range
I get the error.

 
Answer #3    Answered By: Shiv Patel     Answered On: Jan 09

There is a space between " and Maria in the code as posted. When I removed that
space, the code worked fine for me.

 
Answer #4    Answered By: Marnia Mian     Answered On: Jan 09

Not sure how that space got there, it is not present in my module...
I created a new workbook, named a sheet Maria and copied the code
into a new module there - it ran fine.

I have deleted those lines from my code and re-entered them but it
didn't help at all.

I just don't get it.

 
Answer #5    Answered By: Ayaz Hashmi     Answered On: Jan 09

I think sometimes the forum adds a space (especially after a double quote); I
have seen it before.

So far, I am unable to replicate your problem. I have experimented with sheet
protection, merging cells, and setting the scrollarea, but your code works every
time for me. I'll keep trying...

 
Answer #6    Answered By: Zane Thompson     Answered On: Jan 09

After tinkering with
the Cells(30,10).Select, I have found that I can get it to work if I
define it a Activesheet.Cells(30,10).Select. This could be a solution
- It is also hanging when I attempt to select  ranges using
"Range(Cells(1,1),Cells(2,2)).Select", for example. I left that
'problem' at work, and will have to pick it up tomorrow. I am thinking
I can probably mention the worksheet somehow in these types of
statements too and get this thing off the ground again (at least I
hope I can!).

Now that I think of it, this code in my test file was in a module, and
I tied it to a button when I brought it over into the actual file it
was to be used in. Would it make any difference if the code is running
from a command button on another worksheet, and as such, is tied to
that worksheet as opposed to being in a module tied to the workbook??

 
Answer #7    Answered By: Constance Reid     Answered On: Jan 09

Just a thought....
could there already be a "Maria" worksheet that is hidden?

as another test, I typically try other variations of calling a worksheet in my
quest for troubleshooting:

x=Sheets("Maria").index
Sheets(x).select

or

y = Sheets("Maria").CodeName
Sheets(y).Select

 
Answer #8    Answered By: Este Ferrrari     Answered On: Jan 09

OK.. let's give this a shot.
First of all, you're not really selecting a "range".
You're selecting a "cell", specifically the cell at row 30 column 10.
Try this:
Range(cells(30,10)).select
or even:
Worksheets("Maria").Range(Cells(30,10)).Select

If it still doesn't work, put this before your select  statement:

MsgBox Activeworkbook.name & chr(13) & activeworksheet.name

This should show you the name of the workbook and worksheet before you select
the data...

It has to be something that you're doing before you get to this point.

 
Answer #9    Answered By: Channarong Boonliang     Answered On: Jan 09

The Cells() call returns a Range object anyway.

In fact, Range(cells(30,10)).select will return an error.

 
Answer #10    Answered By: Abagail Cohen     Answered On: Jan 09

The cods is going to default to the active sheet. If you've run it from a
button on a different sheet, then that different sheet will be the active
sheet. You might need to specifically qualify the range/cell calls with the
sheet you want.

 
Answer #11    Answered By: Daisy Phillips     Answered On: Jan 09

I have gotten it working agian. our original post got me thinking, and I
realized that what had changed was that the code was tied my "Matrix" worksheet
when I added it to the real workbook; before it was just in a module in the
workbook. I took the code out of the button, code sheet and instead put it back
into a public module, which I then call from the button code sheet. It works
like a charm now!!

 
Didn't find what you were looking for? Find more on Select Method or Range Class failed Or get search suggestion and latest updates.




Tagged: