Method 'SeriesCollection' of object '_chart' failed

xSteveGx

New Member
Joined
Aug 20, 2007
Messages
2
Hi guys I'm getting an error when it reaches
Code:
ActiveChart.SeriesCollection(j).XValues = "=(" & Chart1XVal & ")"
i dont think its related to the variable range being passed as it's in a valid format when i MsgBox it.

Any Ideas

Code:
Sub graphseries()

Dim Chart1XVal As String
Dim Chart1Val As String

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Discussion Sheet").Range("A1:A1"), _
        PlotBy:=xlRows
        
    For j = 1 To Range("NumberCriterium").Value
        Chart1XVal = ""
        Chart1Val = ""
        For i = 1 To Range("NumberSuppliers").Value - 1
            If i = 1 Then
                Chart1XVal = Chart1XVal & Range("AvgSupplier" & i).Name
                Chart1Val = Chart1Val & Range("AvgTotalScoreAdjusted" & i & j).Name
            Else
                Chart1XVal = Chart1XVal & ", " & Range("AvgSupplier" & i).Name
                Chart1Val = Chart1Val & ", " & Range("AvgTotalScoreAdjusted" & i & j).Name
            End If
        Next i
        Chart1XVal = WorksheetFunction.Substitute(Chart1XVal, "=", "")
        Chart1Val = WorksheetFunction.Substitute(Chart1Val, "=", "")
        
        MsgBox Chart1XVal
        MsgBox Chart1Val

        ActiveChart.SeriesCollection(j).XValues = "=(" & Chart1XVal & ")"
        ActiveChart.SeriesCollection(j).Values = "=(" & Chart1Val & ")"
        ActiveChart.SeriesCollection(j).Name = "=AvgCriteria" & j
    Next j
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Evaluated Avg Scores"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Supplier"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Score"
    End With
    ActiveChart.HasDataTable = False
End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

xSteveGx

New Member
Joined
Aug 20, 2007
Messages
2
Richard,

Chart1XVal is returning

'Discussion Sheet'!$G$1, 'Discussion Sheet'!$N$1, etc..........

which is valid because i cut and pasted it into a recorded macro and it worked

I'm stumped
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,049
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top