Whenever I try to define a range with the following
syntax, for example:

Worksheets("Name").Range(Cells(3,3), Cells(2402,3))

I get the following error message:

Run-time error '1004': Application-defined or object-
defined error.

However, defining the range with the following syntax
works just fine:

Worksheets("Name").Range("C3:C2402")

The problem is, I want to be able to treat the row and
column numbers as variables within the code.

The other interesting thing is that the offending method
seems to work once, but then not again, or perhaps only
works if the worksheet happens to be active, which
doesn't help if I'm trying to write to ranges on more
than one worksheet successively.

Can anyone tell me what I'm doing wrong?

Thanks in advance,
Dave Dixon

Dave,

Is sheet 'Name' active when you run this code. If not, it is probably
getting confused as you define the worksheet but then use a Cells reference
on the active sheet. Try

With Worksheets("Name")
.Range(.Cells(3,3), .Cells(2402,3))
etc
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dave" wrote in message
...
Whenever I try to define a range with the following
syntax, for example:

Worksheets("Name").Range(Cells(3,3), Cells(2402,3))

I get the following error message:

Run-time error '1004': Application-defined or object-
defined error.

However, defining the range with the following syntax
works just fine:

Worksheets("Name").Range("C3:C2402")

The problem is, I want to be able to treat the row and
column numbers as variables within the code.

The other interesting thing is that the offending method
seems to work once, but then not again, or perhaps only
works if the worksheet happens to be active, which
doesn't help if I'm trying to write to ranges on more
than one worksheet successively.

Can anyone tell me what I'm doing wrong?

Thanks in advance,
Dave Dixon

Bob,

Thanks for your reply. Based on your comment that VB was
getting confused about which sheet was open, I was able
to fix the problem by breaking up the code into the
following:

Worksheets("Name").Activate
Range(Cells(x,y), Cells(a,b)).Value = WhatEver

Now it is all working without a hitch. The original code
should still have worked though. By qualifying the Range
property with an Object, I shouldn't have had to do it
this way.

Cheers,
Dave Dixon

-----Original Message-----
Dave,

Is sheet 'Name' active when you run this code. If not,
it is probably
getting confused as you define the worksheet but then
use a Cells reference
on the active sheet. Try

With Worksheets("Name")
.Range(.Cells(3,3), .Cells(2402,3))
etc
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dave" wrote in message
...
Whenever I try to define a range with the following
syntax, for example:

Worksheets("Name").Range(Cells(3,3), Cells(2402,3))

I get the following error message:

Run-time error '1004': Application-defined or object-
defined error.

However, defining the range with the following syntax
works just fine:

Worksheets("Name").Range("C3:C2402")

The problem is, I want to be able to treat the row and
column numbers as variables within the code.

The other interesting thing is that the offending
method
seems to work once, but then not again, or perhaps only
works if the worksheet happens to be active, which
doesn't help if I'm trying to write to ranges on more
than one worksheet successively.

Can anyone tell me what I'm doing wrong?

Thanks in advance,
Dave Dixon


.

Dave,

Not if some other worksheet was active as I mentioned.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dave" wrote in message
...
Bob,

Thanks for your reply. Based on your comment that VB was
getting confused about which sheet was open, I was able
to fix the problem by breaking up the code into the
following:

Worksheets("Name").Activate
Range(Cells(x,y), Cells(a,b)).Value = WhatEver

Now it is all working without a hitch. The original code
should still have worked though. By qualifying the Range
property with an Object, I shouldn't have had to do it
this way.

Cheers,
Dave Dixon

-----Original Message-----
Dave,

Is sheet 'Name' active when you run this code. If not,
it is probably
getting confused as you define the worksheet but then
use a Cells reference
on the active sheet. Try

With Worksheets("Name")
.Range(.Cells(3,3), .Cells(2402,3))
etc
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dave" wrote in message
...
Whenever I try to define a range with the following
syntax, for example:

Worksheets("Name").Range(Cells(3,3), Cells(2402,3))

I get the following error message:

Run-time error '1004': Application-defined or object-
defined error.

However, defining the range with the following syntax
works just fine:

Worksheets("Name").Range("C3:C2402")

The problem is, I want to be able to treat the row and
column numbers as variables within the code.

The other interesting thing is that the offending
method
seems to work once, but then not again, or perhaps only
works if the worksheet happens to be active, which
doesn't help if I'm trying to write to ranges on more
than one worksheet successively.

Can anyone tell me what I'm doing wrong?

Thanks in advance,
Dave Dixon


.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dave" wrote in message
...
Bob,

Thanks for your reply. Based on your comment that VB was
getting confused about which sheet was open, I was able
to fix the problem by breaking up the code into the
following:

Worksheets("Name").Activate
Range(Cells(x,y), Cells(a,b)).Value = WhatEver

Now it is all working without a hitch. The original code
should still have worked though. By qualifying the Range
property with an Object, I shouldn't have had to do it
this way.

Cheers,
Dave Dixon

-----Original Message-----
Dave,

Is sheet 'Name' active when you run this code. If not,
it is probably
getting confused as you define the worksheet but then
use a Cells reference
on the active sheet. Try

With Worksheets("Name")
.Range(.Cells(3,3), .Cells(2402,3))
etc
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dave" wrote in message
...
Whenever I try to define a range with the following
syntax, for example:

Worksheets("Name").Range(Cells(3,3), Cells(2402,3))

I get the following error message:

Run-time error '1004': Application-defined or object-
defined error.

However, defining the range with the following syntax
works just fine:

Worksheets("Name").Range("C3:C2402")

The problem is, I want to be able to treat the row and
column numbers as variables within the code.

The other interesting thing is that the offending
method
seems to work once, but then not again, or perhaps only
works if the worksheet happens to be active, which
doesn't help if I'm trying to write to ranges on more
than one worksheet successively.

Can anyone tell me what I'm doing wrong?

Thanks in advance,
Dave Dixon


.

Worksheets("Name").Range(.Cells(3,3), .Cells(2402,3))

Notice the periods - Piku

--
Message posted from http://www.ExcelForum.com

What objects are qualifying those?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"pikus " wrote in message
...
Worksheets("Name").Range(.Cells(3,3), .Cells(2402,3))

Notice the periods - Pikus


---
Message posted from http://www.ExcelForum.com/

In that context .Cells(x, y) is effectively Worksheets
("Name").Cells(x, y). Does that answer your question?

John M.
-----Original Message-----
What objects are qualifying those?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"pikus " wrote in
message
...
Worksheets("Name").Range(.Cells(3,3), .Cells(2402,3))

Notice the periods - Pikus


---
Message posted from http://www.ExcelForum.com/



.

No it doesn't because no it isn't.

My question was pointing out a problem, not seeking enlightenment.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John M." wrote in message
...
In that context .Cells(x, y) is effectively Worksheets
("Name").Cells(x, y). Does that answer your question?

John M.
-----Original Message-----
What objects are qualifying those?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"pikus " wrote in
message
...
Worksheets("Name").Range(.Cells(3,3), .Cells(2402,3))

Notice the periods - Pikus


---
Message posted from http://www.ExcelForum.com/



.