Setting range by cells fails

[Andrew]

Hello,
I have been trying to set a range using the following code:

Dim RNG as Range
Set RNG = worksheets("test").Range(cells(1,1),cells(10,10))

This fails with an error 1004

but if I use this code
Dim RNG as Range
Set RNG = worksheets("test").Range("A1:J10)

The code works.

This is the same range, why won't the compiler accept the first code?

thanks,
andy

 

[Rick Rothstein]

I have been trying to set a range using the following code:

Dim RNG as Range
Set RNG = worksheets("test").Range(cells(1,1),cells(10,10))

This fails with an error 1004

Try qualifying both of those Cells objects with the worksheets they are on
and see if that makes a difference.

Rick Rothstein (MVP - Excel)

 

[reza]

Hello,
I have been trying to set a range using the following code:

Dim RNG as Range
Set RNG = worksheets("test").Range(cells(1,1),cells(10,10))

This fails with an error 1004

but if I use this code
Dim RNG as Range
Set RNG = worksheets("test").Range("A1:J10)

The code works.

This is the same range, why won't the compiler accept the first code?

thanks,
andy

The problem is that function Cells() refers to the current worksheet.
Try this:

Dim RNG As Range
With Worksheets("test")
Set RNG = .Range(.Cells(1, 1), .Cells(10, 10))
End With

/reza

 

[Charabeuh]

Hello,

Cells refers to the active sheet. If your active sheet is not
worksheet("test"), an error will occur.

Try this:

With Worksheets("test")
Set RNG = Range(.Cells(1, 1), .Cells(1, 1))
End With

OR

Set RNG = Range(Worksheets("test").Cells(1, 1), _
Worksheets("test").Cells(3, 3))








Andrew a écrit :

 

[Andrew]

Hello,

Cells refers to the active sheet. If your active sheet is not
worksheet("test"), an error will occur.

Try this:

With Worksheets("test")
  Set RNG = Range(.Cells(1, 1), .Cells(1, 1))
End With

OR

Set RNG = Range(Worksheets("test").Cells(1, 1), _
    Worksheets("test").Cells(3, 3))

Andrew a écrit :

Thanks for the help. It seems that "Set RNG =
worksheets("test").Range(cells(1,1),cells(10,10))"
would default to the cells on the worksheet "test". I'll have to
experiment with this to see what makes it fail.

thanks
Andy