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

Discussion in 'Microsoft Excel Charting' started by Guest, Oct 20, 2004.

  1. Guest

    Guest 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
     
    Guest, Oct 20, 2004
    #1
    1. Advertisements

  2. Guest

    Tushar Mehta Guest

    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

    In article <>,
    says...
    > 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

    {snip}
     
    Tushar Mehta, Oct 20, 2004
    #2
    1. Advertisements

  3. Guest

    Jon Peltier Guest

    Tushar Mehta wrote:

    > Maybe because QCData is both undeclared and uninitialized?


    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.


    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/
    _______
     
    Jon Peltier, Oct 22, 2004
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Unable to set the Values Property of the Series Class

    Guest, Feb 25, 2004, in forum: Microsoft Excel Charting
    Replies:
    0
    Views:
    376
    Guest
    Feb 25, 2004
  2. Guest
    Replies:
    1
    Views:
    764
    Jon Peltier
    May 21, 2004
  3. vjp
    Replies:
    1
    Views:
    449
    Jon Peltier
    Oct 22, 2004
  4. samir
    Replies:
    1
    Views:
    4,212
    Jon Peltier
    Nov 12, 2004
  5. Marco Shaw

    VBA error: Unable to set the Values property of the Series class

    Marco Shaw, Jul 12, 2005, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    7,134
    Guest
    Jul 12, 2005
Loading...

Share This Page