reference format Range(Cells(),Cells())

[Stefi]

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

 

[Norman Jones]

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.

 

[Ken Johnson]

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

 

[Stefi]

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:

 

[Norman Jones]

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.

 

[Norman Jones]

Hi Steffi,

My post dropped the initial lines and should have resd:

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.