I'm trying to code a dynamic chart series (two series), with
one common x-axis. The length of the series, and the
location of the columns holding the series can both change.

I have used the cells method of the range object to do this.
It works sometimes, and other times I get the error shown
in the subject of this email. This is the code:


Dim nrows As Integer, ncolumns As Integer

'sheet containing the data series and xvalues
Sheets("Intermediate Output").Select
'determine number of rows to plot
Range("a1").Select
Selection.End(xlDown).Select
nrows = ActiveCell.Row

'determine number of columns having data
Range("a1").Select
Selection.End(xlToRight).Select
ncolumns = ActiveCell.Column

Charts("BOD Plot").Activate

With Worksheets("intermediate output")
'define ranges for series
ActiveChart.SeriesCollection(1).XValues = _
.Range(.Cells(2, 1), .Cells(nrows, 1))
ActiveChart.SeriesCollection(1).Values = _
.Range(.Cells(2, (ncolumns - 1)), _ .Cells(nrows,
(ncolumns - 1)))
ActiveChart.SeriesCollection(2).XValues = _
.Range(.Cells(2, 1), .Cells(nrows, 1))
ActiveChart.SeriesCollection(2).Values = _
.Range(.Cells(2, (ncolumns - 2)), _ .Cells(nrows,
(ncolumns - 2)))
End With

When it DOESN'T work, if I step through the code and insert
the cursor at various places to attempt to ascertain what
actual values the program is getting for the cells, I may
find something like "(ncolumns-2)=331"! Which doesn't make
any sense to me, as ncolumns itself may only equal 36!!

Does anyone have any ideas for how to fix this, or why it
doesn't work???

Thank you all,
Kate

I'm answering myself, since no one else did. What I discovered is
that, when a chart series' values become based upon cells that are
empty, new values cannot be reassigned programatically! Why? Go
figure, musta seemed like a good idea at the time.

In my particular workbook problem, I have a persistent chart the
values for which are repeatedly erased and replaced as the model is
run. I had to create a hidden sheet with dummy values that I switched
the series to, before the data sheet was erased, and then switched
back to the new data, in order for the series to be updateable. Now
isn't that ridiculous!

-Kate



Kate wrote:
I'm trying to code a dynamic chart series (two series), with
one common x-axis. The length of the series, and the
location of the columns holding the series can both change.

I have used the cells method of the range object to do this.
It works sometimes, and other times I get the error shown
in the subject of this email. This is the code:


Dim nrows As Integer, ncolumns As Integer

'sheet containing the data series and xvalues
Sheets("Intermediate Output").Select
'determine number of rows to plot
Range("a1").Select
Selection.End(xlDown).Select
nrows = ActiveCell.Row

'determine number of columns having data
Range("a1").Select
Selection.End(xlToRight).Select
ncolumns = ActiveCell.Column

Charts("BOD Plot").Activate

With Worksheets("intermediate output")
'define ranges for series
ActiveChart.SeriesCollection(1).XValues = _
.Range(.Cells(2, 1), .Cells(nrows, 1))
ActiveChart.SeriesCollection(1).Values = _
.Range(.Cells(2, (ncolumns - 1)), _ .Cells(nrows,
(ncolumns - 1)))
ActiveChart.SeriesCollection(2).XValues = _
.Range(.Cells(2, 1), .Cells(nrows, 1))
ActiveChart.SeriesCollection(2).Values = _
.Range(.Cells(2, (ncolumns - 2)), _
.Cells(nrows, (ncolumns - 2)))
End With

When it DOESN'T work, if I step through the code and insert the cursor
at various places to attempt to ascertain what actual values the program
is getting for the cells, I may find something like "(ncolumns-2)=331"!
Which doesn't make any sense to me, as ncolumns itself may only equal 36!!

Does anyone have any ideas for how to fix this, or why it doesn't work???

Thank you all,
Kate