peculiar error with Range() object

Try out the following code. If sheet2 is active, all 3 procedures work fine, but if sheet2 is not
active, TestRangeCells will not work but the other 2 procedures work fine. Anyone know why there is
a difference?

Sub TestRangeA1()
Sheets(2).Range("a1:a2") = 99
End Sub

Sub TestRangeCells()
Sheets(2).Range(Cells(1, 1), Cells(2, 1)) = 99
End Sub

Sub TestCells()
Sheets(2).Cells(1, 1) = 99
Sheets(2).Cells(2, 1) = 99
End Sub

When used without a qualifier, Cells function will refer to ActiveSheet

You should write:

With Sheets(2)
.range(.cells(1,1),.cells(2,1)) = 99
end with

HTH
--
AP

"Jeff" <[email protected]> a écrit dans le message de news:
[email protected]...
> Try out the following code. If sheet2 is active, all 3 procedures work
> fine, but if sheet2 is not active, TestRangeCells will not work but the
> other 2 procedures work fine. Anyone know why there is a difference?
>
> Sub TestRangeA1()
> Sheets(2).Range("a1:a2") = 99
> End Sub
>
> Sub TestRangeCells()
> Sheets(2).Range(Cells(1, 1), Cells(2, 1)) = 99
> End Sub
>
> Sub TestCells()
> Sheets(2).Cells(1, 1) = 99
> Sheets(2).Cells(2, 1) = 99
> End Sub
>
>

aaaaaaahhhhh!!! Very nice! THANKS! I was thinking that cells was a property of the range object
and that you only had to qualify .range

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> When used without a qualifier, Cells function will refer to ActiveSheet
>
> You should write:
>
> With Sheets(2)
> .range(.cells(1,1),.cells(2,1)) = 99
> end with
>
> HTH