Unable to get SeriesCollection runtime error
 

Hi, I have code within Excel that is called after choosing
different parameters to plot from a custom toolbar.

If I have chosen to plot only one parameter, the code works fine.
However, if I choose an option that plots several
parameters (one plot for each) at a time, the code returns a
run-time error 1004 as it tries to plot the second chart.

The data for the series ranges for the second chart is valid, and
I can manually plot them. I don't understand why more than one
chart at a time can't be plotted.

Here is a code snippet at where the problem occurs:

Sub Plot_Normal(xlbook, xlsheet)
'for each 'normal' parameter sheet
Dim strName As String

For i = 1 To ncount
' counts number of company entrys for a category
mCCount = 0
Sheets(parameter(i)).Select

....code places data for the parameter(i) (declared a public
array) worksheet into a new range on the sheet passed as xlsheet
to this procedure, for parameter(i)....(code not shown here)
.................................................. ......

' loads ranges of data sets into 'range' variables
Dim rangeX As Range
Dim rangeMet As Range
Dim rangeEng As Range
Dim rangeCX As Range
Dim rangeCMet As Range
Dim DLrange As Range

' ranges for chart series'
Set rangeX = .Range(.Cells(1 + datacount, 3), _
.Cells(mCount(i) + datacount, 3))
Set rangeMet = .Range(.Cells(1 + datacount, 1), _
.Cells(mCount(i) + datacount, 1))
Set rangeEng = .Range(.Cells(1 + datacount, 2), _
.Cells(mCount(i) + datacount, 2))
Set rangeCX = .Range(.Cells(1 + datacount, 5), _
.Cells(mCCount + datacount, 5))
Set rangeCMet = .Range(.Cells(1 + datacount, 4), _
.Cells(mCCount + datacount, 4))
Set DLrange = .Range(.Cells(1 + datacount, 6), _
.Cells(mCCount + datacount, 6))
End With

' plots each category with at least one 'company'
mill in that category
' and formats data series
'chart name is parameter and then category abbrev.
strName = parameter(i) & category(i)

.Charts.Add.Name = strName

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
With .SeriesCollection(1)

at this point, I receive the run-time error "unable to get the
SeriesCollection property of the Chart class."
I've been working on this for about a week, and have just about
lost my mind!!!! As I said, it works once through,
then chokes on the second chart.

Thank you for your help, in advance,
Kate

Unable to get SeriesCollection runtime error
 

At this point:

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
With .SeriesCollection(1)

you should check whether there is a series 1:

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
If .SeriesCollection.Count = 0 Then
.NewSeries
End If
With .SeriesCollection(1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
Hi, I have code within Excel that is called after choosing different
parameters to plot from a custom toolbar.

If I have chosen to plot only one parameter, the code works fine. However,
if I choose an option that plots several
parameters (one plot for each) at a time, the code returns a run-time
error 1004 as it tries to plot the second chart.

The data for the series ranges for the second chart is valid, and I can
manually plot them. I don't understand why more than one chart at a time
can't be plotted.

Here is a code snippet at where the problem occurs:

Sub Plot_Normal(xlbook, xlsheet)
'for each 'normal' parameter sheet
Dim strName As String

For i = 1 To ncount
' counts number of company entrys for a category
mCCount = 0
Sheets(parameter(i)).Select

...code places data for the parameter(i) (declared a public array)
worksheet into a new range on the sheet passed as xlsheet to this
procedure, for parameter(i)....(code not shown here)
.................................................. .....

' loads ranges of data sets into 'range' variables
Dim rangeX As Range
Dim rangeMet As Range
Dim rangeEng As Range
Dim rangeCX As Range
Dim rangeCMet As Range
Dim DLrange As Range

' ranges for chart series'
Set rangeX = .Range(.Cells(1 + datacount, 3), _
.Cells(mCount(i) + datacount, 3))
Set rangeMet = .Range(.Cells(1 + datacount, 1), _
.Cells(mCount(i) + datacount, 1))
Set rangeEng = .Range(.Cells(1 + datacount, 2), _
.Cells(mCount(i) + datacount, 2))
Set rangeCX = .Range(.Cells(1 + datacount, 5), _
.Cells(mCCount + datacount, 5))
Set rangeCMet = .Range(.Cells(1 + datacount, 4), _
.Cells(mCCount + datacount, 4))
Set DLrange = .Range(.Cells(1 + datacount, 6), _
.Cells(mCCount + datacount, 6))
End With

' plots each category with at least one 'company' mill in that
category
' and formats data series
'chart name is parameter and then category abbrev.
strName = parameter(i) & category(i)

.Charts.Add.Name = strName

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
With .SeriesCollection(1)

at this point, I receive the run-time error "unable to get the
SeriesCollection property of the Chart class."
I've been working on this for about a week, and have just about lost my
mind!!!! As I said, it works once through,
then chokes on the second chart.

Thank you for your help, in advance,
Kate

Unable to get SeriesCollection runtime error
 

Jon, I was hoping you'd help me. I've been reading your
responses to other such questions (none of which applied to my
problem!), and you really know your charts.

Woohoo, it worked!! Thanks you so much!! But I'm puzzled; If I
have to specify a new series when creating a chart, why didn't I
have to for the first chart that was made by this code?

Thank you Thank you Thank you! -Kate

Jon Peltier wrote:
At this point:

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
With .SeriesCollection(1)

you should check whether there is a series 1:

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
If .SeriesCollection.Count = 0 Then
.NewSeries
End If
With .SeriesCollection(1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
Hi, I have code within Excel that is called after choosing different
parameters to plot from a custom toolbar.

If I have chosen to plot only one parameter, the code works fine. However,
if I choose an option that plots several
parameters (one plot for each) at a time, the code returns a run-time
error 1004 as it tries to plot the second chart.

The data for the series ranges for the second chart is valid, and I can
manually plot them. I don't understand why more than one chart at a time
can't be plotted.

Here is a code snippet at where the problem occurs:

Sub Plot_Normal(xlbook, xlsheet)
'for each 'normal' parameter sheet
Dim strName As String

For i = 1 To ncount
' counts number of company entrys for a category
mCCount = 0
Sheets(parameter(i)).Select

...code places data for the parameter(i) (declared a public array)
worksheet into a new range on the sheet passed as xlsheet to this
procedure, for parameter(i)....(code not shown here)
.................................................. .....

' loads ranges of data sets into 'range' variables
Dim rangeX As Range
Dim rangeMet As Range
Dim rangeEng As Range
Dim rangeCX As Range
Dim rangeCMet As Range
Dim DLrange As Range

' ranges for chart series'
Set rangeX = .Range(.Cells(1 + datacount, 3), _
.Cells(mCount(i) + datacount, 3))
Set rangeMet = .Range(.Cells(1 + datacount, 1), _
.Cells(mCount(i) + datacount, 1))
Set rangeEng = .Range(.Cells(1 + datacount, 2), _
.Cells(mCount(i) + datacount, 2))
Set rangeCX = .Range(.Cells(1 + datacount, 5), _
.Cells(mCCount + datacount, 5))
Set rangeCMet = .Range(.Cells(1 + datacount, 4), _
.Cells(mCCount + datacount, 4))
Set DLrange = .Range(.Cells(1 + datacount, 6), _
.Cells(mCCount + datacount, 6))
End With

' plots each category with at least one 'company' mill in that
category
' and formats data series
'chart name is parameter and then category abbrev.
strName = parameter(i) & category(i)

.Charts.Add.Name = strName

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
With .SeriesCollection(1)

at this point, I receive the run-time error "unable to get the
SeriesCollection property of the Chart class."
I've been working on this for about a week, and have just about lost my
mind!!!! As I said, it works once through,
then chokes on the second chart.

Thank you for your help, in advance,
Kate

Unable to get SeriesCollection runtime error
 

If the active cell is in the middle of some data when you create the chart,
the chart includes that data. If the active cell is elsewhere, the chart is
an empty shell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
Jon, I was hoping you'd help me. I've been reading your responses to
other such questions (none of which applied to my problem!), and you
really know your charts.

Woohoo, it worked!! Thanks you so much!! But I'm puzzled; If I have to
specify a new series when creating a chart, why didn't I have to for the
first chart that was made by this code?

Thank you Thank you Thank you! -Kate

Jon Peltier wrote:
At this point:

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
With .SeriesCollection(1)

you should check whether there is a series 1:

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
If .SeriesCollection.Count = 0 Then
.NewSeries
End If
With .SeriesCollection(1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
Hi, I have code within Excel that is called after choosing different
parameters to plot from a custom toolbar.

If I have chosen to plot only one parameter, the code works fine.
However, if I choose an option that plots several
parameters (one plot for each) at a time, the code returns a run-time
error 1004 as it tries to plot the second chart.

The data for the series ranges for the second chart is valid, and I can
manually plot them. I don't understand why more than one chart at a
time can't be plotted.

Here is a code snippet at where the problem occurs:

Sub Plot_Normal(xlbook, xlsheet)
'for each 'normal' parameter sheet
Dim strName As String

For i = 1 To ncount
' counts number of company entrys for a category
mCCount = 0
Sheets(parameter(i)).Select

...code places data for the parameter(i) (declared a public array)
worksheet into a new range on the sheet passed as xlsheet to this
procedure, for parameter(i)....(code not shown here)
.................................................. .....

' loads ranges of data sets into 'range' variables
Dim rangeX As Range
Dim rangeMet As Range
Dim rangeEng As Range
Dim rangeCX As Range
Dim rangeCMet As Range
Dim DLrange As Range

' ranges for chart series'
Set rangeX = .Range(.Cells(1 + datacount, 3), _
.Cells(mCount(i) + datacount, 3))
Set rangeMet = .Range(.Cells(1 + datacount, 1), _
.Cells(mCount(i) + datacount, 1))
Set rangeEng = .Range(.Cells(1 + datacount, 2), _
.Cells(mCount(i) + datacount, 2))
Set rangeCX = .Range(.Cells(1 + datacount, 5), _
.Cells(mCCount + datacount, 5))
Set rangeCMet = .Range(.Cells(1 + datacount, 4), _
.Cells(mCCount + datacount, 4))
Set DLrange = .Range(.Cells(1 + datacount, 6), _
.Cells(mCCount + datacount, 6))
End With

' plots each category with at least one 'company' mill in
that category
' and formats data series
'chart name is parameter and then category abbrev.
strName = parameter(i) & category(i)

.Charts.Add.Name = strName

.ActiveChart.ChartType = xlXYScatter
With .ActiveChart
With .SeriesCollection(1)

at this point, I receive the run-time error "unable to get the
SeriesCollection property of the Chart class."
I've been working on this for about a week, and have just about lost my
mind!!!! As I said, it works once through,
then chokes on the second chart.

Thank you for your help, in advance,
Kate