Error 1004 when defining range by cells(r,c) format

[dave]

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 Phillips]

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)

 

[pikus]

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

Notice the periods - Piku

 

[Bob Phillips]

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)

 

[John M.]

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

John M.

 

[dave]

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

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)