+ Reply to Thread
Results 1 to 10 of 10

run-time error '1004' method 'range' of object '_worksheet' failed

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    68

    run-time error '1004' method 'range' of object '_worksheet' failed

    Hi
    I have an excel with the following code:

    Please Login or Register  to view this content.
    I when I try to add new named rages the code does not work and give the error "run-time error '1004' method 'range' of object '_worksheet' failed".
    is there is a limit for the number of named ranges that can be included in such piece of code? how can I fix it?

    Thanks for your help

    //E.B

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: run-time error '1004' method 'range' of object '_worksheet' failed

    Have you checked that all the named ranges are valid?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-30-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: run-time error '1004' method 'range' of object '_worksheet' failed

    Yes I did, even if I add a non-named rage it wont accept it! it feels like the code is limited to a certain number of ranges "22 in this case" which is strange.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: run-time error '1004' method 'range' of object '_worksheet' failed

    The code actually works for me when I create all the named ranges in your code.

    What happens when you try this code?
    Please Login or Register  to view this content.
    That code loops through each of the named ranges individually rather than all of them in one go.

  5. #5
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: run-time error '1004' method 'range' of object '_worksheet' failed

    why not use Union, to return range that has many areas. It's limited to 30 args.
    Also I would suggest add named ranges in array and then loop trough elements in the array and then trough each cell like you do now.

    Edit: That is what Norie did..
    @ Norie: we did our post at the same time
    If you are pleased with a member's answer then use the Star icon to rate it.

  6. #6
    Registered User
    Join Date
    05-30-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: run-time error '1004' method 'range' of object '_worksheet' failed

    It did not work, I tried to test the code in a new sheet with new named ranges, and got the same result, only up to 22 named range will work, if I add the 23rd it gives the Error, here is the complete code I have used in the test sheet:

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: run-time error '1004' method 'range' of object '_worksheet' failed

    Did you try the code I suggested in post #4?

  8. #8
    Registered User
    Join Date
    05-30-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: run-time error '1004' method 'range' of object '_worksheet' failed

    I tried, but did not know how to combine it with the entire code that I have, the one in post#6. Can you look at it and help me with that. I have to say I can so little VBA i.e. doing some basic amendments to a code is OK, but to change the whole code or write a new part of it is something that I cannot .

    /E

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: run-time error '1004' method 'range' of object '_worksheet' failed

    Here's your code from post #6 and my code from post #4 combined.
    Please Login or Register  to view this content.

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: run-time error '1004' method 'range' of object '_worksheet' failed

    range will only accept 255 characters for its argument
    Josie

    if at first you don't succeed try doing it the way your wife told you to

+ 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] Help! Error 1004, method 'Range' of object'_Worksheet' failed.
    By zac_595 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-07-2013, 02:55 PM
  2. [SOLVED] How can I resolve this Run-Time error '1004': Method 'Range' of object '_Worksheet' failed
    By dafella in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2013, 01:16 AM
  3. Run time Error 1004 Method 'Range' object '_Worksheet 'failed
    By Monica1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2013, 07:42 AM
  4. me too - run-time error '1004' method 'range' of object '_worksheet' failed
    By ASAFSWIS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2013, 05:14 PM
  5. [SOLVED] Name a dynamic range - Run-time error 1004 Method 'Range' of object'_Worksheet' failed
    By DavidBW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2012, 08:00 AM

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