Excel - generate multiple series line chart using same column

Question

I have a VBA script that I use to generate multiple line charts in Excel. It used to include 2 series collections per chart (reading from 2 columns) but I since modified it for only one. However now I want it to do 2 series' again but want it to read both collections from the same column. Is this possible?

I've tried modifying the .SeriesCollection(2) to go to the next range further down the column. However this just returns an error 4001.

Sub CreateCharts()
    Dim ws As Worksheet
    Dim ch As Chart
    Dim NumCharts As Integer, ChartName As String, ChartTitle As String, i  As Integer

    Set ws = Sheets("Charts")

    NumCharts = WorksheetFunction.CountA(ws.Rows(2))

    For i = 2 To NumCharts Step 1 '1 column of data per chart
        ChartName = ws.Cells(2, i) '"chrt" & Range(Col1 & 2)
        ChartTitle = ws.Cells(2, i) 'Range(Col1 & 2)
        Set ch = Charts.Add
        With ch
            .ChartType = xlLine
            .SetSourceData Source:=ws.Range(ws.Cells(3, i), ws.Cells(20, i)), _
            PlotBy:=xlColumns 'range of data for each chart
            .SeriesCollection(1).XValues = ws.Range("A3:A20") 'data range of line 1 (test data)
            .SeriesCollection(2).XValues = ws.Range("A21:A38") 'data range of line 2 (Rw curve)
            .Name = ChartName
            .HasTitle = True
            .ChartTitle.Characters.text = "#" & ws.Cells(2, i) '& " " & ws.Cells(1, i)  'remove title 'change to "ws.Cells(2, i)" to see titles
            .ChartTitle.Left = 600

            'HORiZONTAL X AXiS
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.text = "Frequency (Hz)"
            .Axes(xlCategory).MajorTickMark = xlNone
            .Axes(xlCategory).AxisBetweenCategories = False
            .Axes(xlCategory).Border.LineStyle = None

            'VERTiCAL Y AXiS
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.text = "Sound Reduction Index (dB)"
            .Axes(xlValue).TickLabels.NumberFormat = "0"
            .Axes(xlValue).MajorTickMark = xlNone
            .Axes(xlValue).HasMajorGridlines = False
            .Axes(xlValue).MinimumScale = 10 'minimum value on y
            .Axes(xlValue).MaximumScale = 80 'maximum value on y
            .Axes(xlValue).Border.LineStyle = None

            'LEGEND
            .HasLegend = False

            'FONT SPECiFiCATiONS
            .ChartArea.Format.TextFrame2.TextRange.Font.Size = 14
            .ChartArea.Format.TextFrame2.TextRange.Font.Name = "Myriad Pro"
            .ChartArea.Border.LineStyle = xlNone

            'CHART POSiTiON, SiZE & COLOUR
            .PlotArea.Format.Fill.ForeColor.RGB = RGB(242, 242, 242) 'grey background
            .PlotArea.Top = 0
            .PlotArea.Left = 20
            .PlotArea.Height = 440
            .PlotArea.Width = 420

            'CHART LiNE COLOURS
            .SeriesCollection(1).Border.Color = RGB(27, 117, 188) 'first line colour
            '.SeriesCollection(2).Border.Color = RGB(0, 0, 0) 'second line colour
            '.SeriesCollection(2).LineStyle = xlDashDot

        End With
    Next i

End Sub

Here is an image example of what I'm wanting to achieve.

Ahmed AU · Accepted Answer · 2019-09-17 01:33:55Z

1

Code is slightly modified and tested to work as far my understanding of the objective (to create one 2 series charts per column. 1st series Row 3-20 and 2nd series 21 to 38). Only issue with code was absence of SeriesCollection(2). It is modified to add necessary SeriesCollection and to delete if any automatically added series collection exist.

For i = 2 To NumCharts Step 1 '1 column of data per chart
        ChartName = ws.Cells(2, i) '"chrt" & Range(Col1 & 2)
        ChartTitle = ws.Cells(2, i) 'Range(Col1 & 2)
        Set ch = Charts.Add

            'Delete if any automatically added series exist
            For x = ch.SeriesCollection.Count To 1 Step -1
            ch.SeriesCollection(x).Delete
            Next

        With ch
            .ChartType = xlLine
            .SeriesCollection.Add ws.Range(ws.Cells(3, i), ws.Cells(20, i))
            .SeriesCollection.Add ws.Range(ws.Cells(21, i), ws.Cells(38, i))
            .SeriesCollection(1).XValues = ws.Range(ws.Cells(3, 1), ws.Cells(20, 1))
            .SeriesCollection(2).XValues = ws.Range(ws.Cells(21, 1), ws.Cells(38, 1))

            .Name = ChartName

edited Sep 17, 2019 at 1:33

answered Sep 13, 2019 at 19:27

Ahmed AU

2,7772 gold badges9 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adam Gillies Over a year ago

Thanks, this solution works for me! The only thing is the X axis (xlCategory) displays numbers 1 - 18 instead of the values shown in column A (100 to 4000 with gaps). I'm guessing this is because the two collections are from the same column. Is it possible to customise the axis values?

Ahmed AU Over a year ago

Edited answer with added measures to add Xvalues of the series . Hope it will solve the problem (it worked in my test file)

Adam Gillies Over a year ago

Works like a charm! Thanks again!

Collectives™ on Stack Overflow

Excel - generate multiple series line chart using same column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related