Line Chart parameter wrongly populated

[FaridWahidi]

I am writing a macro to create multiple Line Makers Chart for monthly data in the same worksheets. The 1st & 3rd chart working fine but the 2nd chart parameter is wrongly populated to 7 series collection rather than 3 series collection. (Accurate Parameter)

Previously I have used .SetSourceData Source:=rng, it working fine but will be wrongly populated if the monthly data less than 3 months whereby rows more than columns.

Then, I have tried to add series one by one using .SeriesCollection.Add Source:=rng.

Here is the download link to my attachment
https://app.box.com/s/di3fneqtgpfr9xj35yrz


Nothing wrong with this code but why the 2nd chart parameter wrongly populated. Can anyone help me?

    With ws
        LastRow = .Range("B14").End(xlUp).Row
        Set rng_1a = .Range("A2:B" & LastRow)
        Set rng_1b = .Range("C2:C" & LastRow)
        Set rng_1c = .Range("D2:D" & LastRow)
        ShName = .Name
    End With

   [COLOR=#008000]'Count number of months[/COLOR]
    Mon = ws.Range("B2:B" & ws.Range("B14").End(xlUp).Row).count
 
    If Mon = 1 Then
        RPM = "BB Score Trend (Jan" & "'" & Format(Now(), "YY") & ")"
    ElseIf Mon >= 2 And Mon <= 11 Then
        RPM = "BB Score Trend (Jan - " & MonthName(Mon, True) & "'" & Format(Now(), "YY") & ")"
    ElseIf Mon = 12 Then
        RPM = "BB Score Trend (Jan - " & MonthName(Mon, True) & "'" & Format(DateSerial(Year(Date) - 1, Month(Date), 1), "YY") & ")"
    End If


    ws.Select
    Charts.Add
    With ActiveChart
        .ChartType = xlLineMarkers
        .HasTitle = True
        .ChartTitle.Text = RPM
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month" [COLOR=#008000]'X axis name[/COLOR]
        .Axes(xlCategory, xlPrimary).AxisTitle.Left = 635
        .Axes(xlCategory, xlPrimary).AxisTitle.Top = 380
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).HasMajorGridlines = False
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Scoring" [COLOR=#008000]'y-axis name[/COLOR]
        .Axes(xlValue, xlPrimary).AxisTitle.Orientation = xlHorizontal
        .Axes(xlValue, xlPrimary).AxisTitle.Top = xlUpward
        .Axes(xlValue, xlPrimary).AxisTitle.Top = 10
        .Axes(xlValue, xlPrimary).AxisTitle.Left = 60
        .SeriesCollection.Add Source:=rng_1a
        .SeriesCollection.Add Source:=rng_1b
        .SeriesCollection.Add Source:=rng_1c
        .Location Where:=xlLocationAsObject, Name:=ShName
    End With

 

[Worf]

Hi Farid

Please test this:

 With ws
        LastRow_1 = .Range("C29").End(xlUp).Row
        Set rng_2a = .Range("b17:B" & LastRow_1)
        Set rng_2b = .Range("C17:C" & LastRow_1)
        Set rng_2c = .Range("D17:D" & LastRow_1)
        ShName_1 = .Name
    End With
    
    If Mon = 1 Then
        RPM_1 = "RCB Score Trend (Jan" & "'" & Format(Now(), "YY") & ")"
    ElseIf Mon >= 2 And Mon <= 11 Then
        RPM_1 = "RCB Score Trend (Jan - " & MonthName(Mon, True) & "'" & Format(Now(), "YY") & ")"
    ElseIf Mon = 12 Then
        RPM_1 = "RCB Score Trend (Jan - " & MonthName(Mon, True) & "'" & Format(DateSerial(Year(Date) - 1, _
        Month(Date), 1), "YY") & ")"
    End If
  
    Charts.Add
    With ActiveChart
        Do Until .SeriesCollection.count = 3
            .SeriesCollection.NewSeries
        Loop
        .SeriesCollection(1).Values = rng_2a
        .SeriesCollection(2).Values = rng_2b
        .SeriesCollection(3).Values = rng_2c
        .ChartType = xlLineMarkers
        .HasTitle = True
        .ChartTitle.Text = RPM_1
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month" 'X axis name
        .Axes(xlCategory, xlPrimary).AxisTitle.Left = 635
        .Axes(xlCategory, xlPrimary).AxisTitle.Top = 380
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).HasMajorGridlines = False
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Scoring" 'y-axis name
        .Axes(xlValue, xlPrimary).AxisTitle.Orientation = xlHorizontal
        .Axes(xlValue, xlPrimary).AxisTitle.Top = xlUpward
        .Axes(xlValue, xlPrimary).AxisTitle.Top = 10
        .Axes(xlValue, xlPrimary).AxisTitle.Left = 60
        .Location Where:=xlLocationAsObject, Name:=ShName_1
    End With

 

[Worf]

An improvement:

With ActiveChart
    Do Until .SeriesCollection.count < 4
        .SeriesCollection(.SeriesCollection.count).Delete
    Loop
        
    Do Until .SeriesCollection.count = 3
        .SeriesCollection.NewSeries
    Loop
End With

 

[FaridWahidi]

Hi Worf,

Thanks for willing to help me,

First of all, I am really confused why series values is already created even though I the Sourcedata or series not addded
Does it reflected from the 1st chart? then 3rd is working fine. really confused


Are you trying to delete the existing series using loop? but it seems the existing series not deleted. I try both of code

    With ActiveChart
        Do Until .SeriesCollection.count < 4
                 .SeriesCollection(.SeriesCollection.count).Delete
        Loop
  End  With


    With ActiveChart
        Do Until .SeriesCollection.count = 3
                    .SeriesCollection.NewSeries
        Loop
    End  With

Then, .SeriesCollection(1).Values does not work.

[COLOR=#FF0000]        .SeriesCollection(1).Values = rng_2a[/COLOR]
[COLOR=#FF0000]        .SeriesCollection(2).Values = rng_2b[/COLOR]
[COLOR=#FF0000]        .SeriesCollection(3).Values = rng_2c[/COLOR]

 

[Jon Peltier]

.SeriesCollection.Add Source:=rng has a second parameter called PlotBy, which can be xlRows or xlColumns. Try using this:

.SeriesCollection.Add Source:=rng, PlotBy:=xlColumns

 

[FaridWahidi]

Hi Jon,

I have tried this , but still running error,

  Charts.Add
    With ActiveChart
        .ChartType = xlLineMarkers
        .
        .
        .
[COLOR=#FF0000]        .SeriesCollection.Add Source:=rng_1a, PlotBy:=xlColumns[/COLOR]
[COLOR=#FF0000]        .SeriesCollection.Add Source:=rng_1b, PlotBy:=xlColumns[/COLOR]
[COLOR=#FF0000]        .SeriesCollection.Add Source:=rng_1c, PlotBy:=xlColumns[/COLOR]
        .
    End With

if you download my attachment, It would be better to solve my problem.
https://app.box.com/s/di3fneqtgpfr9xj35yrz

I am really frustrated why the 2nd chart running error but the 1st and 3rd chart working fine. (Nothing wrong with code)

One more thing, I found that if we use command "stop" and run the 2nd chart using single step F8, it working fine but the error will be switched to the 3rd chart . .

If the above code cannot be used to create multiple chart?

 

[Jon Peltier]

Sorry, I was thinking SetSourceData, from your earlier post, but typed the wrong thing.

Chart.SetSourceData.Add Source:=rng, PlotBy:=xlColumns

But this is the entire source for the whole chart, not one series.

The entire syntax for SeriesCollection.Add is:

SeriesCollection.Add(Source, [Rowcol As XlRowCol], [SeriesLabels], [CategoryLabels], [Replace]) As Series

So each series is

.SeriesCollection.Add Source:=rng_1a, Rowcol:=xlColumns, SeriesLabels:=False, CategoryLabels:=False, Replace:=False

I'm guessing at the False values for the last three parameters.

 

[Worf]

Hi Farid
Below is a link to my test workbook, containing the code I posted above.
I ran the macro and the three charts were created without problems.

https://dl.dropboxusercontent.com/u/52116836/ENS_Mutiple%20Chart%20Automation.xlsm

 

[FaridWahidi]

Hi Worf,

Sorry, I was thinking that

    Do Until .SeriesCollection.count < 4
        .SeriesCollection(.SeriesCollection.count).Delete
    Loop

is replacing the one you wrote earlier

    Do Until .SeriesCollection.count = 3
        .SeriesCollection.NewSeries
    Loop

It working fine. Thanks a lot for your kind assistance. .


@ Jon,

Thanks a lot.

 

[Worf]

You are welcome