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

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)

On Feb 12, 10:03*pm, Andrew wrote:
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

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 :
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

On Feb 12, 8:32*pm, Charabeuh wrote:
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 :

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

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