+ Reply to Thread
Results 1 to 10 of 10

Excel - ERROR 1004.. Method Range of Object Worksheet Failed

  1. #1
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    32

    Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    Hello

    New to VBA and I'm having trouble identifying where the error is in my code. I believe I have identified the relevant worksheets and checked all the named ranges and advanced filter. I have 2 workbooks that are identical however, I get this error in one and not the other. I have compared the codes and ranges and I can't see anything obvious.

    Main Code:

    Please Login or Register  to view this content.
    Filter:

    Please Login or Register  to view this content.
    Both workbooks are identical so can't understand why one has an error and the other doesn't.
    Unable to post workbook due to sensitive data. Any help would be appreciated.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,220

    Re: Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    Where does the error occur, which code? If unsure, step through the code(s) by clicking inside the code then hitting F8 to go line by line.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    32

    Re: Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    Hi
    Thank you for taking the time to read my post. The error occurs when clicking cmdIss. I copied the filter code above as it is called in the main code as a sub: AdvStkLoc
    Not sure whether I'm doing this right or what I should expect, but I right clicked on the userform to display ALL the codes and it jumps to :
    Please Login or Register  to view this content.
    When I press F8 again it launches the userform as normal.

    I did the same to the filter code and nothing happens there.

    Amita

  4. #4
    Valued Forum Contributor
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    327

    Re: Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    What line does the code stop at when you get the error? (ie: hitting debug on the error dialog takes you to the code and highlights it yellow, which line does it take you to?)

    Without knowing where it fails and what the content actually is I would be hard pressed to give you a specific answer. Based on the error it generally means that the range you are referring to doesnt exist. There could be any number of reasons why:
    • Invalid reference (like A100000 in an xls file) or a named range thats the wrong scope
    • A vague reference (multiple named ranges with the same name but not implicitly calling it within scope)
    • Trying to reference an empty or null range

    Looking at just the code doesnt really tell us much. We have no idea what the underlying data is, on which line the error occurs, or external factors like difference in the 2 files (file type, storage location, etc).

  5. #5
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    32

    Re: Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    Hi
    Thank you for replying.
    The error doesn't stop the code, everything still executes without any obvious problems. Everything is transferred to the sheets correctly. The error is not displayed as a Run Time Error and there is no End or Debug options, just An Error has occurred, Error 1004... Method Range of Object Worksheet Failed, contact administrator, it only shows an OK button. At the moment, I am grateful that the code still works as it would stop our Warehouse until fixed. I am also confused as I thought that having put Exit Sub before the error handler stops the code if there is an error.

    The workbook with the error sits on a network drive for others to access, however it is not shared (I was horrified after spending months on learning and creating this inventory workbook that you are unable to share a macro enabled workbook!!). The warehouse are using Excel 2003 (soon to be upgraded to 2013) and I use 2013. The error did not occur until today. As the code still works, am I able to eliminate this error from popping up? Not sure if this is the right thing to do as I don't know if it could cause problems in other ways.

    Thank you again for responding, any help would be appreciated.
    Amita

  6. #6
    Valued Forum Contributor
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    327

    Re: Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    Ah the code has an error handler. Do this...

    Open the VBA editor (Developer | VBA) or Alt+F11. Go to Tools | Options and select the general tab. Select the radio button "Break on all errors". Hit ok, close the VBA window and re-run. You will now get the dialog with a debug option. Be sure later to go back and change the option back to "debug on unhandled errors" (or whatever you had it set to).

    Then let us know which line it stops on.

  7. #7
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    32

    Re: Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    Hello
    Thank you for your reply. After following your kind instructions, the error is in the following line:

    Please Login or Register  to view this content.
    Iss3 is a list box and displays the result of an advance filter (AdvStkLoc). The named range of the data from the advance filter is "StkatLoc". The user picks (dblClick_Event) the line of stock for issuing to the factory, which populates 2 frames in the userform of text/combo boxes to edit or issue. Applying my limited logic on this, it would appear that when issuing the last line of stock, if nothing left in stock then nothing to display in the list box, this is when the error pops up, otherwise no error. After repopulating the list box I am clearing the TransID and other dependant text/combo boxes in the 2 frames. Me.TransID is the line number auto-assigned by another macro at spread sheet level for each line of data added to the spreadsheets. This is required to ensure the code knows which line to edit or issue. I hope this makes sense. Do I just need to add an IF statement of sorts after AdvStkLoc stating IF "StkatLoc" = "" then msgbox "No stock" else Iss3.rowsource = StkSH.Range("StkatLoc").Address(external:=True. Would that do it?
    Amita
    Last edited by Amita68; 12-06-2017 at 05:36 AM.

  8. #8
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,220

    Re: Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    Based on your logic, adding the if statement could work. It definitely wont hurt to try.

  9. #9
    Registered User
    Join Date
    08-18-2017
    Location
    Leicester, England
    MS-Off Ver
    Office 2013 & 365
    Posts
    32

    Re: Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    Thank you. Yes, I added the If statement and this as eliminated the error.

  10. #10
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,220

    Re: Excel - ERROR 1004.. Method Range of Object Worksheet Failed

    Glad you were able to get it working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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