Is there a definitive unambiguous write-up of issues using the "cells"
property.

The help definitely implies an issue where is says it has to be referenced
as ".cells" if anything other than the active sheet is being referenced. I
fixed one problem this way by making the sheet active.

I have "cells" all over my code and I'm pretty sure this is causing me pain
of the 1004 variety.

Hi Ted,

To avoid potential, somtimes subtle, problems ranges should always fullly be
qualified.

As with any range object, uqualified references will be interpreted by VBA
as relating to the active sheet or, in the case of a sheet module, relating
to the sheet holding the code.

---
Regards,
Norman



"TedS" wrote in message
...
Is there a definitive unambiguous write-up of issues using the "cells"
property.

The help definitely implies an issue where is says it has to be referenced
as ".cells" if anything other than the active sheet is being referenced.
I
fixed one problem this way by making the sheet active.

I have "cells" all over my code and I'm pretty sure this is causing me
pain
of the 1004 variety.

There is no problem if you code properly.

Defensive programming suggests that you should always reference a Range
object via a worksheet object. So always use something like

oWks.Cells(i,j).Value = myVar

or

With oWks
.Range(.cells(i,j),.Cells(i,j+k)).Formula = "=SUM(A1:B1)"
End With

You set oWks as bormal, either as

Set oWks = Worksheets("Sheet1")

or

Set oWks = Activesheet

Programming in this way should ensure you don't have problems, should make
your code more robust as the sheet being worked upon is explicitly
declared/identified, and it will make it easier to maintain in the future,
as it will be simple to identify which sheet is being referenced.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TedS" wrote in message
...
Is there a definitive unambiguous write-up of issues using the "cells"
property.

The help definitely implies an issue where is says it has to be referenced
as ".cells" if anything other than the active sheet is being referenced.
I
fixed one problem this way by making the sheet active.

I have "cells" all over my code and I'm pretty sure this is causing me
pain
of the 1004 variety.

The case I'm sure I nailed was fully qualified, or so I thought:

set myrange=wks.range(cells(r,c),cells(r2,c2))

i fixed it by making the sheet active

i searched my code for other instances of the range(cells,cells) and there
are none ... all other cells references are qualified and of the nature

x=wks.cells(r,c) ... i'm just nervous that this will be unstable

"Bob Phillips" wrote:

There is no problem if you code properly.

Defensive programming suggests that you should always reference a Range
object via a worksheet object. So always use something like

oWks.Cells(i,j).Value = myVar

or

With oWks
.Range(.cells(i,j),.Cells(i,j+k)).Formula = "=SUM(A1:B1)"
End With

You set oWks as bormal, either as

Set oWks = Worksheets("Sheet1")

or

Set oWks = Activesheet

Programming in this way should ensure you don't have problems, should make
your code more robust as the sheet being worked upon is explicitly
declared/identified, and it will make it easier to maintain in the future,
as it will be simple to identify which sheet is being referenced.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TedS" wrote in message
...
Is there a definitive unambiguous write-up of issues using the "cells"
property.

The help definitely implies an issue where is says it has to be referenced
as ".cells" if anything other than the active sheet is being referenced.
I
fixed one problem this way by making the sheet active.

I have "cells" all over my code and I'm pretty sure this is causing me
pain
of the 1004 variety.

Hi Ted,

The case I'm sure I nailed was fully qualified, or so I thought:

set myrange=wks.range(cells(r,c),cells(r2,c2))

To qualify the above expression, try:

With wks
Set myrange = .Range(.Cells(r, c), .Cells(r2, c2))
End With

Note the periods (full stops) in front of each instance of Cells.


---
Regards,
Norman


"TedS" wrote in message
...
The case I'm sure I nailed was fully qualified, or so I thought:

set myrange=wks.range(cells(r,c),cells(r2,c2))

i fixed it by making the sheet active

i searched my code for other instances of the range(cells,cells) and there
are none ... all other cells references are qualified and of the nature

x=wks.cells(r,c) ... i'm just nervous that this will be unstable

"Bob Phillips" wrote:

There is no problem if you code properly.

Defensive programming suggests that you should always reference a Range
object via a worksheet object. So always use something like

oWks.Cells(i,j).Value = myVar

or

With oWks
.Range(.cells(i,j),.Cells(i,j+k)).Formula = "=SUM(A1:B1)"
End With

You set oWks as bormal, either as

Set oWks = Worksheets("Sheet1")

or

Set oWks = Activesheet

Programming in this way should ensure you don't have problems, should
make
your code more robust as the sheet being worked upon is explicitly
declared/identified, and it will make it easier to maintain in the
future,
as it will be simple to identify which sheet is being referenced.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TedS" wrote in message
...
Is there a definitive unambiguous write-up of issues using the "cells"
property.

The help definitely implies an issue where is says it has to be
referenced
as ".cells" if anything other than the active sheet is being
referenced.
I
fixed one problem this way by making the sheet active.

I have "cells" all over my code and I'm pretty sure this is causing me
pain
of the 1004 variety.

You are ignoring what I said, and what Norman also said. Fully qualify it

set myrange=wks.range(cells(r,c),cells(r2,c2))

should be

set myrange=wks.range(wks.cells(r,c),wks.cells(r2,c2))

otherwise the range is referrin g to worksheet wks, cells is referring to
the activesheet and they may differ. In a UI environmnet, you may not be
able to control what is the activesheet, so you should manage it properly.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TedS" wrote in message
...
The case I'm sure I nailed was fully qualified, or so I thought:

set myrange=wks.range(cells(r,c),cells(r2,c2))

i fixed it by making the sheet active

i searched my code for other instances of the range(cells,cells) and there
are none ... all other cells references are qualified and of the nature

x=wks.cells(r,c) ... i'm just nervous that this will be unstable

"Bob Phillips" wrote:

There is no problem if you code properly.

Defensive programming suggests that you should always reference a Range
object via a worksheet object. So always use something like

oWks.Cells(i,j).Value = myVar

or

With oWks
.Range(.cells(i,j),.Cells(i,j+k)).Formula = "=SUM(A1:B1)"
End With

You set oWks as bormal, either as

Set oWks = Worksheets("Sheet1")

or

Set oWks = Activesheet

Programming in this way should ensure you don't have problems, should
make
your code more robust as the sheet being worked upon is explicitly
declared/identified, and it will make it easier to maintain in the
future,
as it will be simple to identify which sheet is being referenced.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TedS" wrote in message
...
Is there a definitive unambiguous write-up of issues using the "cells"
property.

The help definitely implies an issue where is says it has to be
referenced
as ".cells" if anything other than the active sheet is being
referenced.
I
fixed one problem this way by making the sheet active.

I have "cells" all over my code and I'm pretty sure this is causing me
pain
of the 1004 variety.