Results 1 to 5 of 5

Excel Macro Error -- "Subscript out of Range error 9&qu

This is a discussion on Excel Macro Error -- "Subscript out of Range error 9&qu within the Excel Questions forums, part of the Question Forums category; Background -- For nearly one year I have been executing a macro which opens various files, goes to the worksheet ...

  1. #1
    New Member
    Join Date
    Jan 2004
    Posts
    5

    Default Excel Macro Error -- "Subscript out of Range error 9&qu

    Background -- For nearly one year I have been executing a macro which opens various files, goes to the worksheet CY-PY USD, updates a cell, saves each file and closes each file. Following is the specific code executed:

    Workbooks.Open FileName:= _
    "\\Fin Color Group\Forecast Pkg\21.xls", UpdateLinks:=3
    Workbooks("21").Worksheets("CY-PY USD").Activate
    Application.Goto Reference:="R5C4"
    Dim Period_Code As Range
    Set Period_Code = Worksheets("CY-PY USD").Range("D5")
    Period_Code.Formula = VPeriod
    Calculate
    ActiveWorkbook.Save
    ActiveWorkbook.Close

    File Location -- Files reside on a Citrix server based at my office location, with the capability to access the drive and execute macros directly from the office or via internet dial-in.

    Issue -- Our e-mail is Lotus Notes (another story). Two weeks ago my PC was upgraded to a new version of Notes. Ever since the upgrade, I get the "Subscript out of Range error 9" message when the macro attempts to execute the code:

    Workbooks("21").Worksheets("CY-PY USD").Activate

    This error occurs when I am accessing the server directly from my PC while in the office and full up the file on my PC.

    While in the office, when I access the Citrix server via the internet and run the marco on the Citrix server, everything executes okay.

    Any ideas???

    Jim

  2. #2
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default Re: Excel Macro Error -- "Subscript out of Range error

    Jim,

    The only way you should see that error is if you fail to open the workbook. When you are in the environment where your macro is failing, can you open that WB manually? If so, if you type ? activeworkbook.path in the immediate window, does the path correspond to what you're specifying in your code?

    HTH
    Greg

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  3. #3
    New Member
    Join Date
    Jan 2004
    Posts
    5

    Default Re: Excel Macro Error -- "Subscript out of Range error

    Greg

    When I execute the macro it actually does open the file 21.xls in Excel, then it stops on the code to find the CY-PY USD worksheet.

    Jim

  4. #4
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default Re: Excel Macro Error -- "Subscript out of Range error

    Hmmm, try putting a ".xls" at the end of the WB name [Workbooks("21.xls").~]. Otherwise, I'm stumped. Only other subscript is your WS subscript, so unless a space or something crept into your WS name to make the name inside your worksheets() reference invalid (and you'd also have to be opening different copies of the same WB for this to fail one time and not fail another).
    Greg

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  5. #5
    New Member
    Join Date
    Jan 2004
    Posts
    5

    Default Re: Excel Macro Error -- "Subscript out of Range error

    Greg .....

    Blow me away, it worked!!!!!

    Added the .xls and tried it via direct access to the server as well as access via the internet. Both were successful.

    Still can't understand why this happens, but ya cracked the problem.

    Thanks again

    Jim

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com