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

 

[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

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.