Hello again! ![Smilie](https://www.excelforum.com/images/smilies/smile.gif)
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? ![Roll Eyes (Sarcastic)](https://www.excelforum.com/images/smilies/rolleyes.gif)
- Thomas
Bookmarks