CODEWITHSUNDEEP
excelvbachartsexcel-2003
user2271563
user2271563

Reputation: 35

Excel 2003 vba chart failed

So im getting a chart failed error and it seems like it is because of my name WBname but i cant figure it out. Had a similar question earlier but since this is a little different i thought i would make a new question. Here is the code im having issues with:

Dim WBname As String
WBname = Replace(ActiveWorkbook.Name, ".xls", "")
Worksheets(WBname).Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection(1).XValues = Worksheets(WBname).Range("A4:A5000")
ActiveChart.SeriesCollection(1).Values = Worksheets(WBname).Range("B4:B5000")
ActiveChart.SeriesCollection(1).Name = Worksheets(WBname).Range("B3")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = Worksheets(WBname).Range("A4:A5000")
ActiveChart.SeriesCollection(2).Values = Worksheets(WBname).Range("C4:C5000")
ActiveChart.SeriesCollection(2).Name = Worksheets(WBname).Range("C3")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).XValues = Worksheets(WBname).Range("A4:A5000")
ActiveChart.SeriesCollection(3).Values = Worksheets(WBname).Range("D4:D5000")
ActiveChart.SeriesCollection(3).Name = Worksheets(WBname).Range("D3")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).XValues = Worksheets(WBname).Range("A4:A5000")
ActiveChart.SeriesCollection(4).Values = Worksheets(WBname).Range("I4:I5000")
ActiveChart.SeriesCollection(4).Name = Worksheets(WBname).Range("I3")

WBname is coming out like i want but im still getting errors.

Method 'SeriesCollection' of object '_Chart' Failed

The sheet name is dynamically changing and matches WBname FYI.

EDIT: i added 

ActiveChart.SetSourceData Source:=Sheets(WBname).Range("A4:A5000")

and it seems to work

Upvotes: 1

Views: 2981

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

You are getting that error because you are trying to assign values to series which is not there ;)

You need to add the series first and then assign values to it. See this example

Sub Sample()
    Dim WBname As String
    WBname = Replace(ActiveWorkbook.Name, ".xls", "")
    Worksheets(WBname).Activate

    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

    ActiveChart.SeriesCollection.NewSeries '<~~~~~ SEE THIS

    ActiveChart.SeriesCollection(1).XValues = Worksheets(WBname).Range("A4:A5000")
    ActiveChart.SeriesCollection(1).Values = Worksheets(WBname).Range("B4:B5000")
    ActiveChart.SeriesCollection(1).Name = Worksheets(WBname).Range("B3")
End Sub

FOLLOWUP

You are getting extra series sometimes because your cursor is in the data for example in Cell B5 or I10 or some other cell. Excel by default adds the series when you say Charts.Add in such a scenario. Ensure that the none of your data is selected when you run the macro. An alternative is to delete the series collection after Charts.Add. See this example

Sub Sample()
    Dim WBname As String
    Dim i As Long

    WBname = Replace(ActiveWorkbook.Name, ".xls", "")
    Worksheets(WBname).Activate
    Charts.Add

    With ActiveChart
        On Error Resume Next
        For i = .SeriesCollection.Count To 1 Step -1
            .SeriesCollection(i).Delete
        Next i
        On Error GoTo 0

        .ChartType = xlXYScatterSmoothNoMarkers

        .SeriesCollection.NewSeries
        .SeriesCollection(1).XValues = Worksheets(WBname).Range("A4:A5000")
        .SeriesCollection(1).Values = Worksheets(WBname).Range("B4:B5000")
        .SeriesCollection(1).Name = Worksheets(WBname).Range("B3")

        .SeriesCollection.NewSeries
        .SeriesCollection(2).XValues = Worksheets(WBname).Range("A4:A5000")
        .SeriesCollection(2).Values = Worksheets(WBname).Range("C4:C5000")
        .SeriesCollection(2).Name = Worksheets(WBname).Range("C3")

        .SeriesCollection.NewSeries
        .SeriesCollection(3).XValues = Worksheets(WBname).Range("A4:A5000")
        .SeriesCollection(3).Values = Worksheets(WBname).Range("D4:D5000")
        .SeriesCollection(3).Name = Worksheets(WBname).Range("D3")

        .SeriesCollection.NewSeries
        .SeriesCollection(4).XValues = Worksheets(WBname).Range("A4:A5000")
        .SeriesCollection(4).Values = Worksheets(WBname).Range("I4:I5000")
        .SeriesCollection(4).Name = Worksheets(WBname).Range("I3")
    End With
End Sub

Upvotes: 1

mansuetus
mansuetus

Reputation: 64

You're not telling what Sheet you want to work on. (assuming that Workbook is really needed, that is not the case most of the time) :

Workbooks(WBname).Sheets("Sheet1").Range("A4:A5000")

should work better.

(in these case, just use the macro recorder to get the proper way of doing it and adapt it to your needs after)

Upvotes: 0

Related Questions