Range Problem #2 (continued)

[Steven Drenker]

If I assign a worksheet to the variable ws and a range to the variable rng,
why can't I then do a ws.rng.Select?

If I do it in two steps (i.e., ws.Select followed by a rng.Select),
everything works.

Sub test()
Dim rng As Range
Dim ws As Worksheet

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

Worksheets("Sheet2").Select

ws.Select ' <- OK. Selects Sheet1
rng.Select ' <- OK. Selects A1:A10 on Sheet1

Worksheets("Sheet2").Select
' Try to combine ws and rng into one command:
ws.rng.Select '<- Fails: Method or data member (".rng") not found

End Sub

I get the same behavior with the following code:

Sub test2()
Dim rng As Range
Dim str As String

str = "Sheet2!$A$1:$C$5"
Set rng = Range(str)

Worksheets("Sheet1").Select
' rng.Select ' <- Fails: Method 'Select' of object 'Range' failed
rng.Value = "huh?" ' <- Works OK. Changes values on Sheet2 to "huh?"
End Sub

It seems the Range knows which worksheet it is associated with, but the
address of the range does not include the sheet name, only the range
address. And I can only use the sheet reference inherent in a Range to
change values, not navigate.

 

[Tom Ogilvy]

You are correct that a range is a range on a specific sheet.
You are incorrect about the address not including the sheet name. You just
haven't asked properly

msgbox rng.Address(0,1,xlA1,True)
msgbox rng.Address(1,0,xlR1C1,True)

Look at excel vba help at the address property.

 

[Bob Phillips]

Because setting a range always assume a worksheet if you don't explicitly
state it. So

Set rng = Range("A1:A10")

is picking up the range from the activesheet, not ws. You need

Set rng = ws.Range("A1:A10")

and then just use rng in the code, not ws.rng.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)