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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,659
Messages
5,838,645
Members
430,558
Latest member
Krampus

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