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

[Paul]

After trying for quite some time I have narrowed down my problem. For the
first time I am using Ranges. I got an example which searches through column
A (set Range = the column) searches for a value which is in cell B1. The
code was on the same sheet, and there is no problem.
But I wanted to search through column A on Sheet1 and search for a value in
a cell in sheet2. I couldn't get it to work. Finally I have got it working
but not like I want to:

Now I have a button on sheet2, Cell D5 on sheet2 contains the value to be
searched on sheet1. But the button now refers to code on a module1 rather
than on the "sheet2 code area" (so to speak).

As long as I use the code in the module1 I don't get any errors but when I
copy the code to sheet2 I get an error 1004.

It is this code that gives the problems on sheet2 and which works fine on
module1:
***
Set AllCells = Range(Worksheets(1).Cells(1, 1), Worksheets(1).Cells(65536,
1).End(xlUp))
***
I have tried to insert Worksheets(1).activate (which I acually wanted to
avoid), but this doesn't change a thing.

How can I get the Set AllCells range to work on the codepage of sheet2
rather than on module1

TIA

[Tom Ogilvy]

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


"Paul" <PaulNieboer HEREGOESTHEAD Gmail.com> wrote in message
news:[email protected]...
> After trying for quite some time I have narrowed down my problem. For the
> first time I am using Ranges. I got an example which searches through
column
> A (set Range = the column) searches for a value which is in cell B1. The
> code was on the same sheet, and there is no problem.
> But I wanted to search through column A on Sheet1 and search for a value
in
> a cell in sheet2. I couldn't get it to work. Finally I have got it working
> but not like I want to:
>
> Now I have a button on sheet2, Cell D5 on sheet2 contains the value to be
> searched on sheet1. But the button now refers to code on a module1 rather
> than on the "sheet2 code area" (so to speak).
>
> As long as I use the code in the module1 I don't get any errors but when I
> copy the code to sheet2 I get an error 1004.
>
> It is this code that gives the problems on sheet2 and which works fine on
> module1:
> ***
> Set AllCells = Range(Worksheets(1).Cells(1, 1), Worksheets(1).Cells(65536,
> 1).End(xlUp))
> ***
> I have tried to insert Worksheets(1).activate (which I acually wanted to
> avoid), but this doesn't change a thing.
>
> How can I get the Set AllCells range to work on the codepage of sheet2
> rather than on module1
>
> TIA
>
>
>

[Paul]

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

[Tom Ogilvy]

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 Worksheet
set ws = Worksheets("Sheet1")
Set AllCells = Range(ws.Cells(1,1), _
ws.Cells(rows.count,1).End(xlup))

is equivalent to
Dim ws as Worksheet
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:[email protected]...
>
> > 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)
>
>