Error: unable to set the value property of the series class

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a serious problem with a chart that I made using VBA.

I get the error: Runtime error 1004:
"Unable to set the values property of the series class"
and sometimes the same error but just for the named property of the series.

The chart is a standard lines chart.

I hope somebody can help me

Below is the section of code that is flagged:

Sub ChartOptions()
' this sets up the QC Graph for the main lab with Chart title,
' axis title and scale of Y-axis according to each parameter

Sheets("Calcs").Select
ActiveCell = Cells(3, 2)
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave+1*(STDEV)" ' UWL
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave-1*(STDEV)" 'LWL
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave+3*(STDEV)" 'UCL
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave-3*(STDEV)" 'LCL

Range("B3:B7").Select
Selection.Copy
Range("C3:AZ7").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

a = MsgBox("Limits data created. Click Ok to view Graph", vbOKOnly,
"Limits Data")

If a = vbOK Then
Sheets("QC Graph").Select
With Charts("QC Graph") 'this sets up the chart title, axis
title and legend
.HasTitle = True
.ChartTitle.Text = "QC Graph for:" & " " & QCType & " "
& "at the" & " " & QCSection
.SeriesCollection(1).Name = QCType
**** .SeriesCollection(1).Values = QCData *****
End With
With Charts("QC Graph").Axes(xlValue)
.HasTitle = True
.AxisTitle.Text = QCType
End With
End If
'this sets up the various QCtypes' Y-axis scales
If QCSection = "Clean Lab" Then
Select Case SectionNumber
Case 1 To 4 'Cl,SO4,Fe,Cu
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 5
.MaximumScale = 15
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Select
ElseIf QCSection = "Boiler Bench" Then
Select Case SectionNumber
Case 1 'Na
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 5
.MaximumScale = 15
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Case 2 'SiO2
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 15
.MaximumScale = 25
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
End With
End Select
ElseIf QCSection = "Water Bench" Then
Select Case SectionNumber
Case 1 To 2 'M-Alk,CaH
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 85
.MaximumScale = 125
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
End With
Case 3 'COD
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 35
.MaximumScale = 65
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
End With
End Select
ElseIf QCSection = "Micro Lab" Then
Select Case SectionNumber
Case 1 To 3 'HPC,TC,FC
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = Auto
.MaximumScale = Auto
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
End With
End Select
End If

End Sub
 
Maybe because QCData is both undeclared and uninitialized?

It is a very good idea to declare the kind of variable you should be
using. Put a 'Option Explicit' statement at the top of each module.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi

I have a serious problem with a chart that I made using VBA.

I get the error: Runtime error 1004:
"Unable to set the values property of the series class"
and sometimes the same error but just for the named property of the series.

The chart is a standard lines chart.

I hope somebody can help me

Below is the section of code that is flagged:

Sub ChartOptions()
' this sets up the QC Graph for the main lab with Chart title,
' axis title and scale of Y-axis according to each parameter

Sheets("Calcs").Select
ActiveCell = Cells(3, 2)
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave+1*(STDEV)" ' UWL
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave-1*(STDEV)" 'LWL
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave+3*(STDEV)" 'UCL
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave-3*(STDEV)" 'LCL

Range("B3:B7").Select
Selection.Copy
Range("C3:AZ7").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

a = MsgBox("Limits data created. Click Ok to view Graph", vbOKOnly,
"Limits Data")

If a = vbOK Then
Sheets("QC Graph").Select
With Charts("QC Graph") 'this sets up the chart title, axis
title and legend
.HasTitle = True
.ChartTitle.Text = "QC Graph for:" & " " & QCType & " "
& "at the" & " " & QCSection
.SeriesCollection(1).Name = QCType
**** .SeriesCollection(1).Values = QCData *****
End With
Click to expand...
{snip}
 
Tushar said:
Maybe because QCData is both undeclared and uninitialized?
Click to expand...

No. See below.
It is a very good idea to declare the kind of variable you should be
using. Put a 'Option Explicit' statement at the top of each module.
Click to expand...

Very true, despite what I'm going to say.

If you assign a null to the series values at this point, and the series already
contains a valid set of values, Excel will assign a single zero to the values. The
null variable QCData is converted the best Excel knows how. (I tested this twice to
make sure, because at first I thought Tushar nailed it.)

The problem is that the series probably does not contain a valid set of values.
Perhaps it points to a range of blank cells, or errors (like #N/A). If the data is
not sufficient to produce a visible series, you cannot access any of the components
of the series formula. Even if you are trying to set it, not read what it is, you
will get an error.

This accounts for the failure to set the series values. It does not account for the
ability to set its name in the previous line.

For a quick workaround, you can set the chart type temporarily to an Area type:

activechart.SeriesCollection(1).charttype = xlarea

Do what you need to with the series formula and its components, then change the
chart type back.

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