| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting
Hopefully someone will clarify an issue for me that has me baffled. This is
not the first time that selection of a range in another worksheet generates errors. I want to select a range in another worksheet. Excel keeps giving me a run time error '1004'. These errors occur even if I fully qualify the name, such as Worksheets("PI").Range("P").Select. I also recorded a macro to see what it generated for code, then copied that and still received the error. This is the code that I am using currently: Worksheets("PositionImports").Select Range("Positions").Select This is the error with the Range select line highlighted in the IDE: Run time error '1004' Method 'Range' of object '_Worksheet' failed. What gives here? Excel seems to not deal with this issue in a consistent manner. Thank you, Mark |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting
Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect Assumes Range("Positions") is on Sheet PositionImports. I assume you are running this with a command button or in any event the code is in a sheet module. In such cases, the unqualified Range("Positions") is implicitly qualified by the sheet containing the code module. Since it is not on that sheet, you get the error. Explicitly qualifying it removes that problem. -- Regards, Tom Ogilvy "Mark Petruszak" wrote in message ... Hopefully someone will clarify an issue for me that has me baffled. This is not the first time that selection of a range in another worksheet generates errors. I want to select a range in another worksheet. Excel keeps giving me a run time error '1004'. These errors occur even if I fully qualify the name, such as Worksheets("PI").Range("P").Select. I also recorded a macro to see what it generated for code, then copied that and still received the error. This is the code that I am using currently: Worksheets("PositionImports").Select Range("Positions").Select This is the error with the Range select line highlighted in the IDE: Run time error '1004' Method 'Range' of object '_Worksheet' failed. What gives here? Excel seems to not deal with this issue in a consistent manner. Thank you, Mark |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting
Thank you Tom, for supplying the solution and the reasoning. I understand
the logic that you put forth but I don't like it. ;-) Thank you for your help. -Mark "Tom Ogilvy" wrote: Worksheets("PositionImports").Select Worksheets("PositionImports").Range("Positions").S elect Assumes Range("Positions") is on Sheet PositionImports. I assume you are running this with a command button or in any event the code is in a sheet module. In such cases, the unqualified Range("Positions") is implicitly qualified by the sheet containing the code module. Since it is not on that sheet, you get the error. Explicitly qualifying it removes that problem. -- Regards, Tom Ogilvy "Mark Petruszak" wrote in message ... Hopefully someone will clarify an issue for me that has me baffled. This is not the first time that selection of a range in another worksheet generates errors. I want to select a range in another worksheet. Excel keeps giving me a run time error '1004'. These errors occur even if I fully qualify the name, such as Worksheets("PI").Range("P").Select. I also recorded a macro to see what it generated for code, then copied that and still received the error. This is the code that I am using currently: Worksheets("PositionImports").Select Range("Positions").Select This is the error with the Range select line highlighted in the IDE: Run time error '1004' Method 'Range' of object '_Worksheet' failed. What gives here? Excel seems to not deal with this issue in a consistent manner. Thank you, Mark |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting
Tom, are you saying that writing
Worksheets("PositionImports").Range("Positions").S elect resolves the problem? I suppose this makes selecting the worksheet with Worksheets("PositionImports").Select not of any (known) use in this context? Bill "Tom Ogilvy" wrote in message ... Worksheets("PositionImports").Select Worksheets("PositionImports").Range("Positions").S elect Assumes Range("Positions") is on Sheet PositionImports. I assume you are running this with a command button or in any event the code is in a sheet module. In such cases, the unqualified Range("Positions") is implicitly qualified by the sheet containing the code module. Since it is not on that sheet, you get the error. Explicitly qualifying it removes that problem. -- Regards, Tom Ogilvy "Mark Petruszak" wrote in message ... Hopefully someone will clarify an issue for me that has me baffled. This is not the first time that selection of a range in another worksheet generates errors. I want to select a range in another worksheet. Excel keeps giving me a run time error '1004'. These errors occur even if I fully qualify the name, such as Worksheets("PI").Range("P").Select. I also recorded a macro to see what it generated for code, then copied that and still received the error. This is the code that I am using currently: Worksheets("PositionImports").Select Range("Positions").Select This is the error with the Range select line highlighted in the IDE: Run time error '1004' Method 'Range' of object '_Worksheet' failed. What gives here? Excel seems to not deal with this issue in a consistent manner. Thank you, Mark |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting
Hi Bill
I believe waht Tom is saying is that: Worksheets("PositionImports").Select Worksheets("PositionImports").Range("Positions").S elect solves the problem. You can't select a range on a sheet which is not active hence you must first select (or activate) the sheet. Because the code in question is in a sheet module any unqualified range is assumed to be on the sheet containing the code which is why you must qualify the range when selecting it. I hope this makes sense Rowan "William Benson" wrote: Tom, are you saying that writing Worksheets("PositionImports").Range("Positions").S elect resolves the problem? I suppose this makes selecting the worksheet with Worksheets("PositionImports").Select not of any (known) use in this context? Bill "Tom Ogilvy" wrote in message ... Worksheets("PositionImports").Select Worksheets("PositionImports").Range("Positions").S elect Assumes Range("Positions") is on Sheet PositionImports. I assume you are running this with a command button or in any event the code is in a sheet module. In such cases, the unqualified Range("Positions") is implicitly qualified by the sheet containing the code module. Since it is not on that sheet, you get the error. Explicitly qualifying it removes that problem. -- Regards, Tom Ogilvy "Mark Petruszak" wrote in message ... Hopefully someone will clarify an issue for me that has me baffled. This is not the first time that selection of a range in another worksheet generates errors. I want to select a range in another worksheet. Excel keeps giving me a run time error '1004'. These errors occur even if I fully qualify the name, such as Worksheets("PI").Range("P").Select. I also recorded a macro to see what it generated for code, then copied that and still received the error. This is the code that I am using currently: Worksheets("PositionImports").Select Range("Positions").Select This is the error with the Range select line highlighted in the IDE: Run time error '1004' Method 'Range' of object '_Worksheet' failed. What gives here? Excel seems to not deal with this issue in a consistent manner. Thank you, Mark |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting
Both statements are required. The change in behavior i cited is more
restrictive, not less restrictive. Please re read the explanation. -- Regards, Tom Ogilvy "William Benson" wrote in message ... Tom, are you saying that writing Worksheets("PositionImports").Range("Positions").S elect resolves the problem? I suppose this makes selecting the worksheet with Worksheets("PositionImports").Select not of any (known) use in this context? Bill "Tom Ogilvy" wrote in message ... Worksheets("PositionImports").Select Worksheets("PositionImports").Range("Positions").S elect Assumes Range("Positions") is on Sheet PositionImports. I assume you are running this with a command button or in any event the code is in a sheet module. In such cases, the unqualified Range("Positions") is implicitly qualified by the sheet containing the code module. Since it is not on that sheet, you get the error. Explicitly qualifying it removes that problem. -- Regards, Tom Ogilvy "Mark Petruszak" wrote in message ... Hopefully someone will clarify an issue for me that has me baffled. This is not the first time that selection of a range in another worksheet generates errors. I want to select a range in another worksheet. Excel keeps giving me a run time error '1004'. These errors occur even if I fully qualify the name, such as Worksheets("PI").Range("P").Select. I also recorded a macro to see what it generated for code, then copied that and still received the error. This is the code that I am using currently: Worksheets("PositionImports").Select Range("Positions").Select This is the error with the Range select line highlighted in the IDE: Run time error '1004' Method 'Range' of object '_Worksheet' failed. What gives here? Excel seems to not deal with this issue in a consistent manner. Thank you, Mark |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting
Your explanation of previous (which I dutifully re-read) did no good to one
such as me, Tom, who did not know -- yes, after all these years, did not know :-( -- that you cannot select a range on a non-active sheet. Rowan's explanation did the trick. Thanks all. "Tom Ogilvy" wrote in message ... Both statements are required. The change in behavior i cited is more restrictive, not less restrictive. Please re read the explanation. -- Regards, Tom Ogilvy "William Benson" wrote in message ... Tom, are you saying that writing Worksheets("PositionImports").Range("Positions").S elect resolves the problem? I suppose this makes selecting the worksheet with Worksheets("PositionImports").Select not of any (known) use in this context? Bill "Tom Ogilvy" wrote in message ... Worksheets("PositionImports").Select Worksheets("PositionImports").Range("Positions").S elect Assumes Range("Positions") is on Sheet PositionImports. I assume you are running this with a command button or in any event the code is in a sheet module. In such cases, the unqualified Range("Positions") is implicitly qualified by the sheet containing the code module. Since it is not on that sheet, you get the error. Explicitly qualifying it removes that problem. -- Regards, Tom Ogilvy "Mark Petruszak" wrote in message ... Hopefully someone will clarify an issue for me that has me baffled. This is not the first time that selection of a range in another worksheet generates errors. I want to select a range in another worksheet. Excel keeps giving me a run time error '1004'. These errors occur even if I fully qualify the name, such as Worksheets("PI").Range("P").Select. I also recorded a macro to see what it generated for code, then copied that and still received the error. This is the code that I am using currently: Worksheets("PositionImports").Select Range("Positions").Select This is the error with the Range select line highlighted in the IDE: Run time error '1004' Method 'Range' of object '_Worksheet' failed. What gives here? Excel seems to not deal with this issue in a consistent manner. Thank you, Mark |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting
It made sense after you explained to me that you cannot select a range on a
non-active sheet. I have been learning to do nearly everything in the world to ranges without selecting them first, therefore I forgot (or maybe never realized) this fundamental fact. Thanks Rowan! "Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message ... Hi Bill I believe waht Tom is saying is that: Worksheets("PositionImports").Select Worksheets("PositionImports").Range("Positions").S elect solves the problem. You can't select a range on a sheet which is not active hence you must first select (or activate) the sheet. Because the code in question is in a sheet module any unqualified range is assumed to be on the sheet containing the code which is why you must qualify the range when selecting it. I hope this makes sense Rowan "William Benson" wrote: Tom, are you saying that writing Worksheets("PositionImports").Range("Positions").S elect resolves the problem? I suppose this makes selecting the worksheet with Worksheets("PositionImports").Select not of any (known) use in this context? Bill "Tom Ogilvy" wrote in message ... Worksheets("PositionImports").Select Worksheets("PositionImports").Range("Positions").S elect Assumes Range("Positions") is on Sheet PositionImports. I assume you are running this with a command button or in any event the code is in a sheet module. In such cases, the unqualified Range("Positions") is implicitly qualified by the sheet containing the code module. Since it is not on that sheet, you get the error. Explicitly qualifying it removes that problem. -- Regards, Tom Ogilvy "Mark Petruszak" wrote in message ... Hopefully someone will clarify an issue for me that has me baffled. This is not the first time that selection of a range in another worksheet generates errors. I want to select a range in another worksheet. Excel keeps giving me a run time error '1004'. These errors occur even if I fully qualify the name, such as Worksheets("PI").Range("P").Select. I also recorded a macro to see what it generated for code, then copied that and still received the error. This is the code that I am using currently: Worksheets("PositionImports").Select Range("Positions").Select This is the error with the Range select line highlighted in the IDE: Run time error '1004' Method 'Range' of object '_Worksheet' failed. What gives here? Excel seems to not deal with this issue in a consistent manner. Thank you, Mark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting the next row up or down?????? | Excel Discussion (Misc queries) | |||
| SELECTING LAST ROW | Excel Discussion (Misc queries) | |||
| Selecting the Same | Excel Discussion (Misc queries) | |||
| VBA - Selecting a Row | Excel Discussion (Misc queries) | |||
| Selecting The Last Row | Excel Programming | |||
Linear Mode
