+ Reply to Thread
Results 1 to 16 of 16

Method 'Range' of object '_global' failed

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    95

    Method 'Range' of object '_global' failed

    I have tried searching for a solution to this issue before I posted but I couldn't get around the issue.

    Here's part of the code
    Please Login or Register  to view this content.

    My problem is here (right before the text-to-columns bit):
    Please Login or Register  to view this content.
    I get the error mentioned in the title.

    I've also tried:
    Please Login or Register  to view this content.
    But I get the "Select method of Range class failed" error.

    I know that ".Select" is not efficient but I'm trying to run the text-to-columns on P2 until the bottom of that column.

    I also know, from searching, that this is a fairly common issue, so I'd also like to know: what is the cause of this problem?

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Method 'Range' of object '_global' failed

    Please Login or Register  to view this content.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Method 'Range' of object '_global' failed

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    08-20-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    95

    Re: Method 'Range' of object '_global' failed

    Quote Originally Posted by AB33 View Post
    Please Login or Register  to view this content.
    Select method of Range class failed

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Method 'Range' of object '_global' failed

    Look at post #3

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Method 'Range' of object '_global' failed

    This should be like this.....
    Please Login or Register  to view this content.
    @ TMS
    No need for tws.Rows.Count, once tws is activated.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  7. #7
    Registered User
    Join Date
    08-20-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    95

    Re: Method 'Range' of object '_global' failed

    Quote Originally Posted by TMS View Post
    Please Login or Register  to view this content.

    Regards, TMS
    That fixed it. Thanks.

    Is there a reason I need to select the sheet first before I target the range?

    EDIT Thanks sktneer, that worked too.
    Last edited by efarkouh; 10-22-2014 at 03:27 PM.

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Method 'Range' of object '_global' failed

    Or even this will work.....
    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Method 'Range' of object '_global' failed

    It works on my test. There is no need to select any way

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-20-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    95

    Re: Method 'Range' of object '_global' failed

    What's the reason for this error?

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Method 'Range' of object '_global' failed

    @sktneer: I qualified the ranges as part of the process of fixing the fault, and, of course, removing the extra ":O".

    Testing demonstrated that that in itself didn't fix the problem, it was necessary to select the sheet before selecting a range on it. While you are correct in that it doesn't need to be qualified once the sheet is selected, it doesn't hurt and so I left it in.

    Reality is I wouldn't code it this way, so I'm just putting a plaster on a graze, not addressing the root cause.

    @efarkouh: if you want to select a range on a worksheet, you need to select the worksheet first. Best bet is to avoid selecting anything in the first place but that's not the way you've coded it.


    Regards, TMS

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Method 'Range' of object '_global' failed

    This syntax is wrong.

    Please Login or Register  to view this content.
    But this is right
    Please Login or Register  to view this content.

  13. #13
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Method 'Range' of object '_global' failed

    Quote Originally Posted by TMS View Post
    @sktneer: While you are correct in that it doesn't need to be qualified once the sheet is selected, it doesn't hurt and so I left it in.

    Reality is I wouldn't code it this way, so I'm just putting a plaster on a graze, not addressing the root cause.
    You mean code with perfection.

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Method 'Range' of object '_global' failed

    You mean code with perfection.
    I try, I try ... and sometimes I succeed

  15. #15
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Method 'Range' of object '_global' failed

    Quote Originally Posted by TMS View Post
    I try, I try ... and sometimes I succeed
    That, in fact, is a good practice.

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Method 'Range' of object '_global' failed

    Maybe this, without selecting sheets or ranges:

    Please Login or Register  to view this content.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Method 'range' of object'_global' failed
    By mdovey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-12-2013, 02:57 PM
  2. Method 'Range' of object'_Global' failed
    By arbulgazar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2013, 06:52 PM
  3. Method 'range' of object '_global' failed.
    By johnmerlino in forum Excel General
    Replies: 3
    Last Post: 02-25-2011, 03:07 PM
  4. Method 'Range' of object '_Global' failed
    By fundoo_pc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2006, 08:10 PM
  5. What does XLS VB Method 'Range of object' _Global failed mean?
    By DCMKGG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1