Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Problem #2 (continued)
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Problem #2 (continued)
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. -- Regards, Tom Ogilvy "Steven Drenker" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Problem #2 (continued)
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) "Steven Drenker" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average With < Continued | Excel Worksheet Functions | |||
Median continued | Excel Discussion (Misc queries) | |||
If then statement continued! | Excel Discussion (Misc queries) | |||
Continued SumProduct Woes | Excel Programming | |||
continued help needed | Excel Programming |