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

Discussion in 'Microsoft Excel Programming' started by Paul, Apr 7, 2005.

  1. Paul

    Paul Guest

    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, Apr 7, 2005
    #1
    1. Advertisements

  2. Paul

    Tom Ogilvy Guest

    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:...
    > 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, Apr 7, 2005
    #2
    1. Advertisements

  3. Paul

    Paul Guest


    > 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)
     
    Paul, Apr 7, 2005
    #3
  4. Paul

    Tom Ogilvy Guest

    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:...
    >
    > > 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, Apr 7, 2005
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. haisat
    Replies:
    0
    Views:
    1,124
    haisat
    Oct 20, 2003
  2. Mohan

    Runime Error 1004 Method Range of Object Global Failed

    Mohan, May 21, 2004, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    588
    Frank Kabel
    May 21, 2004
  3. dreamz

    runtime error 1004 method range of object global failed

    dreamz, Jan 26, 2006, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    458
  4. Guest
    Replies:
    4
    Views:
    539
    Guest
    Feb 12, 2006
  5. bjwade62

    Error 1004 Method 'Range' of object '_Global' failed

    bjwade62, Aug 7, 2006, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    346
    Bernie Deitrick
    Aug 7, 2006
Loading...

Share This Page