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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>>
>>
>>

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" <[email protected]> wrote in message
news:%232Bs%[email protected]...
> 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