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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

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