Selection Range

C

ch

My VBA sometimes works fine with
range("A1").select

Bt sometimes, it shows an error unless I include a "activesheet" in front as
below :
activesheet.range("A1").select

Anyone please help me know why the additional "word" is needed ?
 
B

Bob Phillips

Could it be that it is a chart sheet that is active, that is a sheet that
doesn't have a range.
 
C

ch

The original text did not turn out correctly. I have removed the inverted
commas in the following lines.

My VBA sometimes works fine with
range(A1).select

Bt sometimes, it shows an error unless I include a activesheet in front as
below :
activesheet.range(A1).select

Anyone please help me know why the additional "word" is needed ?
 
D

Dave Peterson

My bet is that
range("a1").select
works fine in a General module, but you're having trouble when the code is in a
worksheet module.

When you type:
range("a1").select
Then this unqualified range will refer to the activesheet in a General module,
but it'll refer to the sheet that owns the code when it's in the worksheet
module.

And I'm betting you do something like this (behind sheet1):

worksheets("Sheet2").select
range("a1").select

Since that second line is still refering to sheet1 and since you can't select a
range on a sheet that is not active, then you get the error.

You could do:
with worksheets("sheet2")
.select
.range("a1").select
end with

But if you're changing a value (say), you could just work on it directly:

worksheets("sheet2").range("a1").value = "hi There!"

Then you don't need to select the sheet or the range.
 
C

ch

Thanks Dave! You found the source of my problem!

Dave Peterson said:
My bet is that
range("a1").select
works fine in a General module, but you're having trouble when the code is in a
worksheet module.

When you type:
range("a1").select
Then this unqualified range will refer to the activesheet in a General module,
but it'll refer to the sheet that owns the code when it's in the worksheet
module.

And I'm betting you do something like this (behind sheet1):

worksheets("Sheet2").select
range("a1").select

Since that second line is still refering to sheet1 and since you can't select a
range on a sheet that is not active, then you get the error.

You could do:
with worksheets("sheet2")
.select
.range("a1").select
end with

But if you're changing a value (say), you could just work on it directly:

worksheets("sheet2").range("a1").value = "hi There!"

Then you don't need to select the sheet or the range.
Click to expand...
 
Top