Selecting a Range of cells in VBA
 

Hello,

I keep having problem with the range() method in VBA. While it seems t
be a straightforward method, I cannot apply it with consistent success
Even worse, I trigger the command the same way, but sometimes it work
fine while other times I kept getting the "select method of range clas
failed". For example, I created a button that had only these tw
lines:

sheet9.activate
sheet9.range("a10:e15").select

These two lines worked fine, but I don't understand why I had t
activate the sheet since I already specify the sheet to look for in m
range() method.

Even worse, when I tried to use the cells() method to specify th
range:

sheet9.activate
sheet9.range(cells(10,1),cells(15,5)).select

the abovementioned error occur. I have no idea why the failure or ho
to fix the problem. I just don't understand. Every single book I hav
talks about the range method as though it's the simplest method ther
is (that's what I thought too).

I would truly appreciate if anybody can explain this to me

--
Message posted from http://www.ExcelForum.com

Selecting a Range of cells in VBA
 

You can't select a range on an inactive sheet.

I could not replicate your second problem.

--

Vasant

"spacecityguy " wrote in
message ...
Hello,

I keep having problem with the range() method in VBA. While it seems to
be a straightforward method, I cannot apply it with consistent success.
Even worse, I trigger the command the same way, but sometimes it works
fine while other times I kept getting the "select method of range class
failed". For example, I created a button that had only these two
lines:

sheet9.activate
sheet9.range("a10:e15").select

These two lines worked fine, but I don't understand why I had to
activate the sheet since I already specify the sheet to look for in my
range() method.

Even worse, when I tried to use the cells() method to specify the
range:

sheet9.activate
sheet9.range(cells(10,1),cells(15,5)).select

the abovementioned error occur. I have no idea why the failure or how
to fix the problem. I just don't understand. Every single book I have
talks about the range method as though it's the simplest method there
is (that's what I thought too).

I would truly appreciate if anybody can explain this to me.


---
Message posted from http://www.ExcelForum.com/

Selecting a Range of cells in VBA
 

I'm guessing that this code is behind a worksheet (not sheet9, too).

sheet9.activate
sheet9.range(cells(10,1),cells(15,5)).select

Unqualified ranges belong to the worksheet that holds the code.

so if this code were behind Sheet1, you're really typing:

sheet9.range(sheet1.cells(10,1),sheet1.cells(15,5) ).select

you could qualify it longhand:
sheet9.range(sheet9.cells(10,1),sheet9.cells(15,5) ).select

but maybe easier reading/typing:

with sheet9
.range(.cells(10,1),.cells(15,5)).select
end with

That said, there's not a lot that you have to select to work on.

You could just work on things directly:

with sheet9
.range(.cells(10,1),.cells(15,5)).clearcontents
end with
and drop the .selects and .activates.




"spacecityguy <" wrote:

Hello,

I keep having problem with the range() method in VBA. While it seems to
be a straightforward method, I cannot apply it with consistent success.
Even worse, I trigger the command the same way, but sometimes it works
fine while other times I kept getting the "select method of range class
failed". For example, I created a button that had only these two
lines:

sheet9.activate
sheet9.range("a10:e15").select

These two lines worked fine, but I don't understand why I had to
activate the sheet since I already specify the sheet to look for in my
range() method.

Even worse, when I tried to use the cells() method to specify the
range:

sheet9.activate
sheet9.range(cells(10,1),cells(15,5)).select

the abovementioned error occur. I have no idea why the failure or how
to fix the problem. I just don't understand. Every single book I have
talks about the range method as though it's the simplest method there
is (that's what I thought too).

I would truly appreciate if anybody can explain this to me.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Selecting a Range of cells in VBA
 

Thanks a lot fellas for shedding a guiding light

--
Message posted from http://www.ExcelForum.com