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" <[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