+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Please tell me why I get a "Run-time error '9' Subscript out of range"

Hybrid View

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    Cincinnati, OH
    MS-Off Ver
    Excel 2007
    Posts
    40

    Question Please tell me why I get a "Run-time error '9' Subscript out of range"

    Hello again!

    In the code below I am attempting to add a new series to a chart, after the user defines the Y-axis value to use in drawing the line. They get three lines to define (High Alarm, Set Point and Low Alarm) and I have only added the new series code to the first possibility, where they entered a value in cell $D$4. Note: The graph is on the sheet "Cond Graph" as is the cells where they enter the y-axis values, but the data used by the graph is on the sheet "AIO-Cond".

    When I enter a new value into "Cond Graph" cell $D$4, the code below halts with an error:
    "Run-time error '9':
    Subscript out of range"

    And the line in the code below that is highlighted by the Debug is (not quite halfway down):
    "With Worksheets("Cond Graph")"

    Sub Worksheet_Change(ByVal Target As Range)
        
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
        
        ' Find the Row number for the last cell in the imported data, put in "LastRow"
        With Worksheets("Data Import").Range("A1")
            LastRow = LastInRow(Worksheets("Data Import").Range("A1"))
        End With
        
        ' Now copy the user defined value to the appropriate column on the device data page
        ' and create new Series on chart for user defined limit line.
        If Target.Address = "$D$4" Then
            With Worksheets("AIO-Cond")
                .Range(.Range("L4"), .Range("L" & LastRow)).Value = Target.Value
            End With
            With Worksheets("Cond Graph")
                Worksheets("Cond Graph").ChartObjects("Chart 1").Activate
                ActiveChart.SeriesCollection.NewSeries
                ActiveChart.SeriesCollection(2).Name = "='Cond Graphs'!$C$4"
                ActiveChart.SeriesCollection(2).Values = _
                "='AIO-Cond'!$A$4:$A & LastRow,'AIO-Cond'!$B$4:$B &LastRow,'AIO-Cond'!$L4:$L & LastRow"
            End With
        End If
    
        If Target.Address = "$D$5" Then
            With Sheets("AIO-Cond")
                .Range(.Range("M4"), .Range("M" & LastRow)).Value = Target.Value
            End With
        End If
    
        If Target.Address = "$D$6" Then
            With Sheets("AIO-Cond")
                .Range(.Range("N4"), .Range("N" & LastRow)).Value = Target.Value
            End With
        End If
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub
    As usual, I have no idea what that error means, or more correctly why I am getting that error. Can any of you nice folks help?

    - Thomas
    Last edited by ThomasHaller; 12-15-2011 at 10:59 AM.

  2. #2
    Registered User
    Join Date
    12-05-2011
    Location
    Cincinnati, OH
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Please tell me why I get a "Run-time error '9' Subscript out of range"

    Doh!

    I think the fact that the spreadsheet I am trying to reference is named "Cond Graphs" (notice the "s" at the end) and not "Cond Graph" might have something to do with the error!

    Solved! :D

    - Thomas

+ 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