Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error | Excel Discussion (Misc queries) | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Help: runtime error - Method seriescollection object_chart failed | Charts and Charting in Excel | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Runtime Error 9 | Excel Discussion (Misc queries) |