+ Reply to Thread
Results 1 to 16 of 16

Subscript is out of range Error

  1. #1
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Subscript is out of range Error

    Hello All,

    It was discussed last week in a previous Forum Post about the Subscript is out of range Error featured below that I was getting and the question was put... Were both Workbooks open during the code functioning?

    At home and at work - both workbook were open.. The code functions ok here at the house but the exact same files fail at the office... Why - What is the Answer?

    Here is the image from work:
    error1.jpg[error2.jpg

    Any Ideas would be great - I need to get the code to work at the office like at the house!

    John
    Last edited by JJFletcher; 04-21-2016 at 07:33 AM.

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

    Re: Subscript is out of range Error - Continues to error at work but not home

    John

    On either machine is Windows set up to display worksheet extensions?

    Also, are the workbooks being opened in the same instance of Excel on both machines?
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Subscript is out of range Error - Continues to error at work but not home

    Hi Norie,

    I do not know how to determine if the machine at work or home is set to display worksheet extensions... These files are going to be used in many states, in many work locations and need to work including this question needing to be solved then...

    The workbooks are opened Separate as illustrated in the image attached... This is for both locations.

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

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    How are you opening the workbooks?

  5. #5
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    Hi Norie,

    First, to address the question you asked about " Is Windows set up to display worksheet extensions?.. I went online and discovered how to determine this (The Internet is wonderful). At home it is set to view... when I get to work today I will check there and reply...

    Your question...:How am I opening the workbooks? - I am selecting Excel from the All Apps feature in windows 10 and then once Excel 2016 is running - I select file, open and then select each file individually and open separately... this is at the house... At work I am selecting Excel from the All Apps feature in windows 7 and then once Excel 2010 is running - I select file, open and then select each file individually and open separately...

    I will reply this afternoon when I return from work and have addressed the question of "worksheet extensions display".

    John
    Last edited by JJFletcher; 04-19-2016 at 07:49 AM. Reason: grammer

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    The screenshot you posted looks like two separate Excel 2010 instances, which would explain your error. The workbooks must be open in the same instance of Excel for the code to work.

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

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    Rory

    That's what I was thinking, perhaps it's an Excel 2016 thing/setting.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    2016 is SDI so separate windows.

  9. #9
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    Hi Folks -

    So how do I assure that the excel files are opened and run in the same instance... At I launched both Excel files separate and the code failed - got the Out of Range Error... There has to be a more final solution to perform this code

    Possibly there is a special code that can be added to force the macro to be in range?

    John
    Last edited by JJFletcher; 04-20-2016 at 07:06 AM.

  10. #10
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    Good Morning,

    I was wondering if there are any fresh thoughts on this - seems to only function at home and not work.. everything is the same unless missing something... maybe there is a better macro to use?

    John

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

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    John

    How about having code in one of the workbooks open the other workbook and when that happens create a reference to the newly opened workbook which can be used in subsequent code?

  12. #12
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    Hi Norie,

    Thanks for your reply.... Below is a screen shot of the code working here at the house - Why here and not work - everything is the same?.....

    works1.jpg

    John
    Last edited by JJFletcher; 04-20-2016 at 07:51 AM. Reason: spelling

  13. #13
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    Excel 2016 is SDI which means that each workbook has its own completely separate application window. Excel 2010 is MDI which means all workbooks are contained in one application window, unless you open separate instances of Excel. If you use the File - Open function from inside Excel, you should get one instance and only one set of toolbars and the code should work.

  14. #14
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Subscript is out of range Error - Continues to error at work...What is the Answer?

    Hi Folks,

    In have success at work and the code worked... It was as said... needed to be launched from a single instance...

    Now it's time for a modification.

    As you can see the Macro is calling the range from the following code:

    Please Login or Register  to view this content.
    Is it possible that this code be modified to look at the computer's Calendar and as the month changes, the range specified in the code can then be modified or automatically adjusted to place the Range being copied and placed in the appropriate column - For Example:

    Worksheets("Goals").Range("F17") is for the month of April..... Worksheets("Goals").Range("G17") is for the month of May..... and so on - so when the user selects copy - the code see's the month and copies the range to the correct column - any thoughts on this?

    John
    Last edited by JJFletcher; 04-20-2016 at 10:04 PM. Reason: Grammer

  15. #15
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Subscript is out of range Error - Finally works at the Office - Have new challenge!

    Good Morning...

    Anyone have any idea's or suggestions on this new question above?

    Thanks,

    John

  16. #16
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Subscript is out of range Error

    Thanks for all of your help - It is appreciated!

    John

+ 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] Run-time error '9': subscript out of range - error occurs on multiple computers except one
    By BrettE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2014, 11:19 PM
  2. Runtime Error - Subscript out of Range / Object Error
    By JHRice in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2012, 05:14 PM
  3. Defining Array - Runtime error 9, Subscript out of range error
    By MaartenW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-03-2012, 07:32 AM
  4. Subscript out of range error
    By agasi999 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-04-2008, 08:13 AM
  5. Runtime Error - Subscript out of range despite On Error statement
    By DoctorG in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-28-2006, 11:05 AM
  6. Subscript out of range error - save copy error
    By bg18461 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2006, 11:53 AM
  7. Type Mismatch error & subscript out of range error
    By Jeff Wright in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2005, 03: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