PC Review


Reply
Thread Tools Rate Thread

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

 
 
Paul
Guest
Posts: n/a
 
      7th Apr 2005
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



 
Reply With Quote
 
 
 
 
Tom Ogilvy
Guest
Posts: n/a
 
      7th Apr 2005
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:(E-Mail Removed)...
> 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
>
>
>



 
Reply With Quote
 
 
 
 
Paul
Guest
Posts: n/a
 
      7th Apr 2005

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


 
Reply With Quote
 
Tom Ogilvy
Guest
Posts: n/a
 
      7th Apr 2005
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:(E-Mail Removed)...
>
> > 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)
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Method "Method 'Open' of object 'Workbooks' failed priustoric@yahoo.com Microsoft Excel Programming 2 2nd Jun 2010 05:28 PM
Http Post Error: A connection attempt failed because the connectedparty did not properly respond after a period of time, or establishedconnection failed because connected host has failed to respond. Deepu Microsoft Dot NET Framework 1 9th Jul 2009 05:09 PM
run-time error, method 'paste' of object - worksheet failed. =?Utf-8?B?Q2FybA==?= Microsoft Excel Programming 1 29th Mar 2006 05:08 AM
Adding named range gives error "method range of object _Global failed " Gunnar Johansson Microsoft Excel Programming 3 10th Aug 2004 01:54 PM
"Run-time error '1004'" Method 'Range' of object '_global' failed. haisat Microsoft Excel Programming 0 20th Oct 2003 12:13 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:15 PM.