Thanks Thanks:  0
Results 1 to 10 of 10

Thread: Runtime error '9':Subscript out of range

  1. #1
    New Member
    Join Date
    Jul 2002
    Location
    Northwest Tennessee
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Runtime error '9':Subscript out of range

    Got a problem:
    When I open more than 1 spreadsheet I get the "Runtime error '9':Subscript out of range" error message. It does not happen opening just one??

    My macro(s) are as follows:
    In This Workbook:
    -----
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    With ActiveSheet.PageSetup
    .LeftFooter = "&10&F"
    .CenterFooter = ""
    .RightFooter = "&10PsafetyŠ January 2001"
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    End With
    End Sub

    ------
    In Module1

    Sub Auto_Open()
    Application.DefaultFilePath = CurDir()
    With Application
    .DisplayCommentIndicator = xlCommentIndicatorOnly
    .Calculation = xlCalculationAutomatic
    .Iteration = True
    .MaxIterations = 500
    .MaxChange = 0.0001
    End With
    ActiveWorkbook.PrecisionAsDisplayed = False
    Sheets("Average Liq").Select
    Range("A1").Select
    End Sub
    ------

    I've tried isolating individual lines, it appears the sheet selection is the culprit, but the recorder provides the same syntax.

    (Each spreadsheet has the same macros, of course sheet name is unique.)

    Ideas???

    Don

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error '9':Subscript out of range

    Try commenting out or deleting this line:
    Application.DefaultFilePath = CurDir()

    Then save the workbook, close it, quit Excel, launch Excel, and try opening a few workbooks. Any luck?

  3. #3
    New Member
    Join Date
    Jul 2002
    Location
    Northwest Tennessee
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Didn't work Tom.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error '9':Subscript out of range

    Take a close look at the *exact* spelling of the worksheet named "Average Liq". Is the sheet tab spelled exactly the way you have it in the macro? With the space in between "Average" and "Liq"? the same spelling too?

  5. #5
    New Member
    Join Date
    Jul 2002
    Location
    Northwest Tennessee
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Same thinking Tom, I used the recorder to assure spelling/spacing.

    Keep in mind, if I open only one spreadsheet there is no error. This is what has me stumped....

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try reeplacing coe with this

    Code:
    ActiveWorkbook.PrecisionAsDisplayed = False 
    Sheets("Average Liq").Select 
    Range("A1").Select
    Code:
    With ThisWorkbook
        .PrecisionAsDisplayed = False
        .Sheets("Average Liq").Select
        .ActiveSheet.Range("A1").Select
    End With
    Kind Regards,
    Ivan F Moala From the City of Sails

  7. #7
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error '9':Subscript out of range

    Well when you get the run time error and hit Debug, what code line is highlighted as being in error?

  8. #8
    New Member
    Join Date
    Jul 2002
    Location
    Northwest Tennessee
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error '9':Subscript out of range

    Ivan,
    Tried your change. I received the following:

    -----
    Run-time error '1004'
    Select method of Worksheet failed

    --------

    Tom,
    In both cases debug is grayed out; not an option.

    Don

  9. #9
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error '9':Subscript out of range

    Unprotect the VBE so when the error occurs again the Debug button will be enabled (Tools > VBA Project Properties > Protection tab, deselect "Lock project for viewing", click OK), save the workbook, close it, reopen it, and see what you get now.

    I would still recommend you take another look at the spelling difference between the sheet tab and the code. Maybe the tab has one space in between the two words and the code has two spaces...stuff like that.

    By the way, is the sheet named Average Liq hidden? If so, unhide it before opening the workbook the next time.

  10. #10
    New Member
    Join Date
    Jul 2002
    Location
    Northwest Tennessee
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom/Ivan,

    The solution; as Kepnor Tregoe would insist, look for change. As I pointed out the code worked when a single worksheet was opened. The problem was only evident when multiple worksheets were opened.

    My belief is that Excel is opening all the workbooks before executing any code, thus it was attempting to select a sheet not available in the workbook that was active. Based on this I changed this code:

    Sheets("Average Liq").Select
    Range("A1").Select

    -to this-

    Workbooks("average liq.xls").Worksheets("Average Liq").Activate
    Range("A1").Select

    It may not the the best approach, but it works. Thanks guys for your ideas, I can always expect help from this forum.

    Don Coffman

Some videos you may like

User Tag List

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
  •