Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Range Problem #2 (continued)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Steven Drenker - 23 Jan 2006 19:00 GMT
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 - 23 Jan 2006 19:11 GMT
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.

Signature

Regards,
Tom Ogilvy

> 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?
[quoted text clipped - 39 lines]
> address. And I can only use the sheet reference inherent in a Range to
> change values, not navigate.
Bob Phillips - 23 Jan 2006 19:16 GMT
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)

> 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?
[quoted text clipped - 39 lines]
> address. And I can only use the sheet reference inherent in a Range to
> change values, not navigate.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.