| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
When to Activate?
The following works fine
Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents The following does not (one line) Worksheets("pivot").Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents The following does (two lines): Worksheets("pivot").Activate Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents Why is that? When is "Activate" required? Why is the following not working? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
When to Activate?
Antonio,
In the statement Worksheets("pivot").Range(Cells(number_s + 3, 1), _ Cells(number_ + 2, 5)).ClearContents The Cells property is NOT refering the cells on the Pivot worksheet. They are refering to cells on the Active Sheet. If the "Pivot" worksheet is not the Active Sheet, you'll get an Error 1004. You need to make the Cells properties reference the Pivot sheet. Use a With statement as follows: With Worksheets("pivot") .Range(.Cells(number_s + 3, 1), _ .Cells(number_ + 2, 5)).ClearContents End With Note the "." character before "Range" and both "Cells". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Antonio" wrote in message ... The following works fine Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents The following does not (one line) Worksheets("pivot").Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents The following does (two lines): Worksheets("pivot").Activate Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents Why is that? When is "Activate" required? Why is the following not working? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
When to Activate?
Hi Chip,
Understood. I use the With extensively. I tried to shorten the code and the dots and overlooked that. Many thanks for the explanation that is very relevant. Regards, Antonio "Chip Pearson" wrote: Antonio, In the statement Worksheets("pivot").Range(Cells(number_s + 3, 1), _ Cells(number_ + 2, 5)).ClearContents The Cells property is NOT refering the cells on the Pivot worksheet. They are refering to cells on the Active Sheet. If the "Pivot" worksheet is not the Active Sheet, you'll get an Error 1004. You need to make the Cells properties reference the Pivot sheet. Use a With statement as follows: With Worksheets("pivot") .Range(.Cells(number_s + 3, 1), _ .Cells(number_ + 2, 5)).ClearContents End With Note the "." character before "Range" and both "Cells". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Antonio" wrote in message ... The following works fine Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents The following does not (one line) Worksheets("pivot").Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents The following does (two lines): Worksheets("pivot").Activate Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents Why is that? When is "Activate" required? Why is the following not working? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
When to Activate?
Hi again,
One thing though. Shouldn't the following line have the same potential problem? Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents The question is that I have used a similar one for different worksheets, that are not the active sheet, and they all work fine. Here the Rows() property does seem to refer to the worksheet specified at the beginning of the object. What am I missing. Many thanks, Antonio "Antonio" wrote: Hi Chip, Understood. I use the With extensively. I tried to shorten the code and the dots and overlooked that. Many thanks for the explanation that is very relevant. Regards, Antonio "Chip Pearson" wrote: Antonio, In the statement Worksheets("pivot").Range(Cells(number_s + 3, 1), _ Cells(number_ + 2, 5)).ClearContents The Cells property is NOT refering the cells on the Pivot worksheet. They are refering to cells on the Active Sheet. If the "Pivot" worksheet is not the Active Sheet, you'll get an Error 1004. You need to make the Cells properties reference the Pivot sheet. Use a With statement as follows: With Worksheets("pivot") .Range(.Cells(number_s + 3, 1), _ .Cells(number_ + 2, 5)).ClearContents End With Note the "." character before "Range" and both "Cells". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Antonio" wrote in message ... The following works fine Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents The following does not (one line) Worksheets("pivot").Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents The following does (two lines): Worksheets("pivot").Activate Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents Why is that? When is "Activate" required? Why is the following not working? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
When to Activate?
No, it should not have the same problem. You're referencing the
Rows property of the worksheet EQ by P. There is no stray property that would be referencing the active sheet by default. "Antonio" wrote in message ... Hi again, One thing though. Shouldn't the following line have the same potential problem? Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents The question is that I have used a similar one for different worksheets, that are not the active sheet, and they all work fine. Here the Rows() property does seem to refer to the worksheet specified at the beginning of the object. What am I missing. Many thanks, Antonio "Antonio" wrote: Hi Chip, Understood. I use the With extensively. I tried to shorten the code and the dots and overlooked that. Many thanks for the explanation that is very relevant. Regards, Antonio "Chip Pearson" wrote: Antonio, In the statement Worksheets("pivot").Range(Cells(number_s + 3, 1), _ Cells(number_ + 2, 5)).ClearContents The Cells property is NOT refering the cells on the Pivot worksheet. They are refering to cells on the Active Sheet. If the "Pivot" worksheet is not the Active Sheet, you'll get an Error 1004. You need to make the Cells properties reference the Pivot sheet. Use a With statement as follows: With Worksheets("pivot") .Range(.Cells(number_s + 3, 1), _ .Cells(number_ + 2, 5)).ClearContents End With Note the "." character before "Range" and both "Cells". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Antonio" wrote in message ... The following works fine Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents The following does not (one line) Worksheets("pivot").Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents The following does (two lines): Worksheets("pivot").Activate Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents Why is that? When is "Activate" required? Why is the following not working? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
When to Activate?
..Rows() belongs to the "EQ by P" worksheet.
But those other things are just numbers (I'm guessing). It's kind of like writing: Worksheets("EQ by P").Rows("12:15").ClearContents Objects belong to something (ranges have a parent of the worksheet, worksheets have a parent of the workbook, and workbooks have a parent of the application). But numbers are just plain old numbers--they don't belong to any object. === 1.2 the 2 doesn't really belong to the 1 <vbg. Antonio wrote: Hi again, One thing though. Shouldn't the following line have the same potential problem? Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents The question is that I have used a similar one for different worksheets, that are not the active sheet, and they all work fine. Here the Rows() property does seem to refer to the worksheet specified at the beginning of the object. What am I missing. Many thanks, Antonio "Antonio" wrote: Hi Chip, Understood. I use the With extensively. I tried to shorten the code and the dots and overlooked that. Many thanks for the explanation that is very relevant. Regards, Antonio "Chip Pearson" wrote: Antonio, In the statement Worksheets("pivot").Range(Cells(number_s + 3, 1), _ Cells(number_ + 2, 5)).ClearContents The Cells property is NOT refering the cells on the Pivot worksheet. They are refering to cells on the Active Sheet. If the "Pivot" worksheet is not the Active Sheet, you'll get an Error 1004. You need to make the Cells properties reference the Pivot sheet. Use a With statement as follows: With Worksheets("pivot") .Range(.Cells(number_s + 3, 1), _ .Cells(number_ + 2, 5)).ClearContents End With Note the "." character before "Range" and both "Cells". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Antonio" wrote in message ... The following works fine Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ + 3)).ClearContents The following does not (one line) Worksheets("pivot").Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents The following does (two lines): Worksheets("pivot").Activate Range(Cells(number_s + 3, 1), Cells(number_ + 2, 5)).ClearContents Why is that? When is "Activate" required? Why is the following not working? -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| activate opened workbook via VBA | Excel Discussion (Misc queries) | |||
| How to activate my Microsoft 2003 system reject product key | Setting up and Configuration of Excel | |||
| How to auto activate macro when file opened | Excel Worksheet Functions | |||
| activate workbook w/unknown name | Excel Discussion (Misc queries) | |||
| How do I activate Document Version management in Excel just like . | Excel Discussion (Misc queries) | |||
Linear Mode
