Re: Range Question / error 1004: method Range of object Worksheet has failed

Tech-Archive recommends: Fix windows errors by optimizing your registry

In a worksheet module, the unqualified use of the term RANGE refers to the
sheet containing the code, so in the Sheet2 code module

Dim ws as Work***
set ws = Worksheets("Sheet1")
Set AllCells = Range(ws.Cells(1,1), _
ws.Cells(rows.count,1).End(xlup))

is equivalent to
Dim ws as Work***
set ws = Worksheets("Sheet1")
Set AllCells = Worksheets("Sheet2").Range(ws.Cells(1,1), _
ws.Cells(rows.count,1).End(xlup))

Which causes an error since the ranges are on different sheets.

In a general module, the unqualifed Range doesn't appear to have this
restricition.

--
Regards,
Tom Ogilvy


"Paul" <PaulNieboer HEREGOESTHEAD Gmail.com> wrote in message
news:uCZ0yb3OFHA.1392@xxxxxxxxxxxxxxxxxxxxxxx
>
> > Set AllCells = worksheets(1).Range(Worksheets(1).Cells(1, 1), _
> > Worksheets(1).Cells(65536,1).End(xlUp))
> >
> > or more concise
> >
> > with Worksheets(1)
> > Set AllCells = .Range(.Cells(1, 1), _
> > .Cells(65536,1).End(xlUp))
> > End With
> >
> > --
> > Regards,
> > Tom Ogilvy
>
>
> Cheers Tom,
>
> I would never had thought about putting worksheets in front of, and right
> after RANGE. I have been looking quite some time in this newgroups and
> others for this answer!!
>
> Now it works like a charm, thanks!!! (btw I now use the With...End With
for
> obvious reasons)
>
>


.

Quantcast