Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference format Range(Cells(),Cells())
Hi All,
I wanted to use ThisWorkbook.Worksheets("shname").Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents but it failed with a runtime error 1004. When I changed the code either to ThisWorkbook.Worksheets("shname").Select Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents or - having transformed colNo1 to colLetter1 and colNo2 to colLetter2 - to ThisWorkbook.Worksheets("shname").Range(colLetter1 & 2 & ":" & colLetter2 & 2)).ClearContents both version worked. I'd like to know WHY this happens, is it my fault or is it an XL bug? Regards, Stefi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference format Range(Cells(),Cells())
Hi Steffi,
You need fully to qualify the ranges, e.g.: With ThisWorkbook.Worksheets("shname") .Range(.Cells(2, colNo1), .Cells(2, colNo2)).ClearContents End With If you do not qualify the Cells expressions, the code will assume that the cells relate to the active sheet. If the active sheet is not the shname sheet this will cause the run time 1004 error as it is not possible to construct a range on the shname sheet which comprises cells from another sheet. Of course, if the code were run whilst shname was active, no error would ensue. It remains good practice, however, always fully to qualify ranges. --- Regards, Norman "Stefi" wrote in message ... Hi All, I wanted to use ThisWorkbook.Worksheets("shname").Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents but it failed with a runtime error 1004. When I changed the code either to ThisWorkbook.Worksheets("shname").Select Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents or - having transformed colNo1 to colLetter1 and colNo2 to colLetter2 - to ThisWorkbook.Worksheets("shname").Range(colLetter1 & 2 & ":" & colLetter2 & 2)).ClearContents both version worked. I'd like to know WHY this happens, is it my fault or is it an XL bug? Regards, Stefi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference format Range(Cells(),Cells())
Hi Stefi,
When applying the Cells Property to a sheet that is not currently active you have to fully specify it. If Sheet2 is active and you want to refer to the range A1:E10 on Sheet1 then you must use: Sheet1.Range(Sheet1.Cells(1,1),Sheet1.Cells(10,5)) not ... Sheet1.Range(Cells(1,1),Cells(10,5)) A With / End With simplifies it to... With Sheet1 ..Range(.Cells(1,1),.Cells(10,5)) etc End With In your case, the first change made the Sheet you were refering to the ActiveSheet, and the second change was not using the Cells property (or method, what ever it is) Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference format Range(Cells(),Cells())
Thanks Norman, I see now how can one use Cells with reference to a worksheet
other than the active sheet. But I still don't understand why these two types of range referencing Range(colLetter1 & 2 & ":" & colLetter2 & 2)) and Range(Cells(2, colNo1), Cells(2, colNo2)) require different type referencing to other worksheets? Thanks, Stefi €žNorman Jones€ť ezt Ă*rta: Hi Steffi, You need fully to qualify the ranges, e.g.: With ThisWorkbook.Worksheets("shname") .Range(.Cells(2, colNo1), .Cells(2, colNo2)).ClearContents End With If you do not qualify the Cells expressions, the code will assume that the cells relate to the active sheet. If the active sheet is not the shname sheet this will cause the run time 1004 error as it is not possible to construct a range on the shname sheet which comprises cells from another sheet. Of course, if the code were run whilst shname was active, no error would ensue. It remains good practice, however, always fully to qualify ranges. --- Regards, Norman "Stefi" wrote in message ... Hi All, I wanted to use ThisWorkbook.Worksheets("shname").Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents but it failed with a runtime error 1004. When I changed the code either to ThisWorkbook.Worksheets("shname").Select Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents or - having transformed colNo1 to colLetter1 and colNo2 to colLetter2 - to ThisWorkbook.Worksheets("shname").Range(colLetter1 & 2 & ":" & colLetter2 & 2)).ClearContents both version worked. I'd like to know WHY this happens, is it my fault or is it an XL bug? Regards, Stefi |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference format Range(Cells(),Cells())
Hi Steffi,
ThisWorkbook.Worksheets("shname"). _ Range(ActiveSheet.Cells(2, colNo1), ActiveSheet.Cells(2, colNo2)). _ ClearContents As previously indicated, this will fail if the active sheet is not the shname sheet. --- Regards, Norman "Stefi" wrote in message ... Thanks Norman, I see now how can one use Cells with reference to a worksheet other than the active sheet. But I still don't understand why these two types of range referencing Range(colLetter1 & 2 & ":" & colLetter2 & 2)) and Range(Cells(2, colNo1), Cells(2, colNo2)) require different type referencing to other worksheets? Thanks, Stefi "Norman Jones" ezt írta: Hi Steffi, You need fully to qualify the ranges, e.g.: With ThisWorkbook.Worksheets("shname") .Range(.Cells(2, colNo1), .Cells(2, colNo2)).ClearContents End With If you do not qualify the Cells expressions, the code will assume that the cells relate to the active sheet. If the active sheet is not the shname sheet this will cause the run time 1004 error as it is not possible to construct a range on the shname sheet which comprises cells from another sheet. Of course, if the code were run whilst shname was active, no error would ensue. It remains good practice, however, always fully to qualify ranges. --- Regards, Norman "Stefi" wrote in message ... Hi All, I wanted to use ThisWorkbook.Worksheets("shname").Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents but it failed with a runtime error 1004. When I changed the code either to ThisWorkbook.Worksheets("shname").Select Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents or - having transformed colNo1 to colLetter1 and colNo2 to colLetter2 - to ThisWorkbook.Worksheets("shname").Range(colLetter1 & 2 & ":" & colLetter2 & 2)).ClearContents both version worked. I'd like to know WHY this happens, is it my fault or is it an XL bug? Regards, Stefi |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference format Range(Cells(),Cells())
Hi Steffi,
My post dropped the initial lines and should have resd: ThisWorkbook.Worksheets("shname").Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents is equivalent to: ThisWorkbook.Worksheets("shname"). _ Range(ActiveSheet.Cells(2, colNo1), ActiveSheet.Cells(2, colNo2)). _ ClearContents As previously indicated, this will fail if the active sheet is not the shname sheet. --- Regards, Norman "Norman Jones" wrote in message ... Hi Steffi, ThisWorkbook.Worksheets("shname"). _ Range(ActiveSheet.Cells(2, colNo1), ActiveSheet.Cells(2, colNo2)). _ ClearContents As previously indicated, this will fail if the active sheet is not the shname sheet. --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox AddItem code to reference cells in a Range | Excel Discussion (Misc queries) | |||
changing absolute reference for range of cells | Excel Worksheet Functions | |||
Range of cells: Convert relative reference into absolute | Excel Discussion (Misc queries) | |||
Formula to return the reference of a range of cells | Excel Discussion (Misc queries) | |||
How do I reference the same cell as I move through range of cells. | Excel Worksheet Functions |