Range Problem

[Steven Drenker]

I'm having trouble understanding a simple behavior of Ranges. In the
following snippet...

Sub test()
Dim rng As Range

Worksheets("Sheet1").Select
Set rng = Range("A1:A10")

Charts("Chart1").Select

Debug.Print rng.Address ' OK - Prints $A$1:$A$10

rng.Value = 10 ' <-- Works OK
rng.Select ' <-- Fails: Run-time Error 1004: Method
'Select' of object 'Range' failed
End Sub

If I can write to the range with "rng.Value =", why can't I select the range
with "rng.Select"? It seems to me both should work.

Steve

 

[Chip Pearson]

You set rng to Sheet1!A1:A10, but then you select a Chart sheet.
You can't select a cell that is not on the active sheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:BFFA6405.1391EA%[email protected]...

 

[Guest]

The object with the focus is your chart, if you reactivate the worksheet your
range select statement works just fine.

ActiveWorkbook.Sheets("Sheet1").Activate

 

[Guest]

Hi Steven

Range("A1:A10") refers to a range of cells
Range 10 is not a range of cells, hence the error message.
Also, you can only set ranges with Set, not with Value

 

[Steven Drenker]

You set rng to Sheet1!A1:A10, but then you select a Chart sheet.
You can't select a cell that is not on the active sheet.

Thanks, Chip. So I need to do it in two steps? First select the sheet and
then select the cell? I can't combine into one step such as ws.range.select?

 

[Chip Pearson]

Yes, you must first select the sheet, then the cell. You can do
it on one line of code with Application.Goto. E.g.,

Application.Goto Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message