Range Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven Drenker - 23 Jan 2006 18:44 GMT

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

Reply to this Message

Chip Pearson - 23 Jan 2006 18:51 GMT

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.

Signature

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

> I'm having trouble understanding a simple behavior of Ranges.
> In the
[quoted text clipped - 20 lines]
>
> Steve

Reply to this Message

Steven Drenker - 23 Jan 2006 19:06 GMT

> 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?

Reply to this Message

Chip Pearson - 23 Jan 2006 19:15 GMT

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

Signature

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

> in article efl423EIGHA.1180@TK2MSFTNGP09.phx.gbl, Chip Pearson
> at
[quoted text clipped - 8 lines]
> then select the cell? I can't combine into one step such as
> ws.range.select?

Reply to this Message

Kevin B - 23 Jan 2006 19:01 GMT

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

ActiveWorkbook.Sheets("Sheet1").Activate

Signature

Kevin Backmann

> I'm having trouble understanding a simple behavior of Ranges. In the
> following snippet...
[quoted text clipped - 18 lines]
>
> Steve

Reply to this Message

Kassie - 23 Jan 2006 19:06 GMT

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

> I'm having trouble understanding a simple behavior of Ranges. In the
> following snippet...
[quoted text clipped - 18 lines]
>
> Steve

Reply to this Message