Plotting multiple data on the same chart

Question

Currently, my code only plots chart for alternate column of y "mean values" in the region from Column A to E with same x values(represented as work week). But now if I want to include the data from another region such as the entire alternate column of y "ideal mean values" which also has the same x values as highlighted in figure 1 onto the same chart, how do i include this data too for plotting in VBA?

figure 1

Currently

Expected

current output

Expected output

current code

Sub plotgraphs()

Call meangraph

End Sub

Private Sub meangraph()
    Dim i As Long, c As Long
    Dim shp As Shape
    Dim Cht As chart, co As Shape
    Dim rngDB As Range, rngX As Range, rngY As Range,yourOtherRange As Range, rngdb1 As Range
    Dim Srs As Series
    Dim ws As Worksheet

    Set ws = Sheets("Data")

    Set rngDB = ws.Range("A1").CurrentRegion

    Set rngX = rngDB.Columns(1)
    Set rngY = rngDB.Columns(2)



    Do While Application.CountA(rngY) > 0

        Set co = Worksheets("meangraphs").Shapes.AddChart
        Set Cht = co.chart

        With Cht
            .ChartType = xlXYScatter
            'remove any data which might have been
            '  picked up when adding the chart
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
            'add the data
            With .SeriesCollection.NewSeries()
                .XValues = rngX.Value
                .Values = rngY.Value

            End With
            'formatting...
            With Cht.Axes(xlValue)
                .MinimumScale = 5
                .MaximumScale = 20
                .TickLabels.NumberFormat = "0.00E+00"
            End With
            Cht.Axes(xlCategory, xlPrimary).HasTitle = True
            Cht.Axes(xlValue, xlPrimary).HasTitle = True
        End With
          Set rngY = rngY.Offset(0, 2) 'next y values




With Cht
    .ChartType = xlXYScatter
    'remove any data which might have been
    '  picked up when adding the chart
    Do While .SeriesCollection.Count > 0
        .SeriesCollection(1).Delete
    Loop
    'add the first series
    With .SeriesCollection.NewSeries()
        .XValues = rngX.Value
        .Values = yourOtherRange.Value
    End With

    'second series
    With .SeriesCollection.NewSeries()
        .XValues = rngX.Value
        .Values = yourOtherRange.Offset(0, 6).Value
    End With
end with



    Loop


end sub

What have you already tried? You should be able to use your existing code as a guide for how to add a new series. Don't just post the answer you got from your previous question with a new requirement added. If you don't understand what your existing code does, then take some time to Google the various parts of it so you can get a better understanding. — Tim Williams
– Tim Williams, Commented Mar 24, 2020 at 17:42
@Tim Williams Sorry, i always have a habit of asking questions here first before i continue to search on google for help..So far, i have included ActiveChart.SetSourceData Source to select other region to plot the data onto the same chart before the loop ends but i couldn't find a way to make the source data selects alternate columns... — cena
– cena, Commented Mar 25, 2020 at 1:47
Google first, then try something, then post if you're still unable to figure it out. You don't need SetSourceData though - repeat the With .SeriesData.NewSeries() part: all you need to do is swap out rngY.Value for yourOtherRange.Value — Tim Williams
– Tim Williams, Commented Mar 25, 2020 at 2:20
@TimWilliams i tried implement the code based on what you suggested, but i got the invalid/unqualified reference at this point With .SeriesData.NewSeries() which im not sure how to go about fixing this problem — cena
– cena, Commented Mar 25, 2020 at 2:37
@TimWilliams oh yeah i forget to mention that i have already updated the code.. — cena
– cena, Commented Mar 25, 2020 at 2:40

Dy.Lee · Accepted Answer · 2020-03-25 04:53:53Z

2

Try this. Your chart is better suited to a linear chart than a distributed one.

Sub plotgraphs()

Call meangraph

End Sub

Private Sub meangraph()
    Dim i As Long, c As Long
    Dim r As Integer, n As Integer
    Dim k As Integer
    Dim Shp As Shape
    Dim Cht As Chart, co As Shape
    Dim rngDB As Range, rngX As Range
    Dim rngY() As Range, rngY2() As Range
    Dim rng As Range
    Dim Srs As Series
    Dim Ws As Worksheet
    Dim rngShp As Range



    Set Ws = Sheets("Data")


    With Ws
        Set rngDB = .Range("A1", .Cells(1, Columns.Count).End(xlToLeft))
        Set rngX = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
        r = rngX.Rows.Count
    End With
    For Each rng In rngDB
        If InStr(rng, "mean") Then
            If Len(rng) = 5 Then
                n = n + 1
                ReDim Preserve rngY(1 To n)
                Set rngY(n) = rng.Offset(1, 0).Resize(r)
            Else
                c = c + 1
                ReDim Preserve rngY2(1 To c)
                Set rngY2(c) = rng.Offset(1, 0).Resize(r)
            End If
        End If
    Next rng
    k = 2
    For i = 1 To n '<~~~ Loop
         Set rngShp = Ws.Range("b" & k).Resize(10, 20)
         k = k + 11
         Set co = Worksheets("meangraphs").Shapes.AddChart
         Set Cht = co.Chart
         With co
            .Top = rngShp.Top
            .Left = rngShp.Left
            .Width = rngShp.Width
            .Height = rngShp.Height
        End With
         With Cht
             '.ChartType = xlXYScatter
             .ChartType = xlLineMarkers
             'remove any data which might have been
             '  picked up when adding the chart
             Do While .SeriesCollection.Count > 0
                 .SeriesCollection(1).Delete
             Loop
             'add the data
             'For i = 1 To n '<~~~ Loop
                 Set Srs = .SeriesCollection.NewSeries
                 With Srs
                     .XValues = rngX
                     .Values = rngY(i)
                     .Format.Line.Visible = msoFalse
                     .MarkerStyle = xlMarkerStyleCircle
                     .MarkerSize = 5
                 End With
                 Set Srs = .SeriesCollection.NewSeries
                 With Srs
                     .XValues = rngX
                     .Values = rngY2(i)
                     .Format.Line.Visible = msoFalse
                     .MarkerStyle = xlMarkerStyleCircle
                     .MarkerSize = 5
                 End With

             'Next i
             'formatting...
             With Cht.Axes(xlValue)
                 .MinimumScale = 5
                 .MaximumScale = 20
                 .TickLabels.NumberFormat = "0.00E+00"
             End With
             Cht.Axes(xlCategory, xlPrimary).HasTitle = True
             Cht.Axes(xlValue, xlPrimary).HasTitle = True

         End With
    Next i
End Sub

edited Mar 25, 2020 at 4:53

answered Mar 25, 2020 at 2:54

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

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

6 Comments

cena Over a year ago

Thanks, i suppose this is only for 1 case? Because i need to use do while loop to plot chart for all alternating y column values which include Same column x values columnA, with y values from column D and column J as well if you get what i mean..

cena Over a year ago

so as shown in my updated post, i will get many charts with multiple data plot on same chart..

Dy.Lee Over a year ago

@cena, need to know what rules your data is located in.

cena Over a year ago

so all the chart will use the same x values that lies in the column A. The y values are plotted based on the values under the header mean 1 and under the header ideal mean 1 in the same chart. So the next chart will be y values of entire column of mean 2 and ideal mean 2 then next chart mean 3 and ideal mean3 as shown in the 1st picture

cena Over a year ago

And the y values of mean 1 lies in alternate column in region A1 and y values of ideal mean 1 lies in alternate column in region H1 onwards

|

Tim Williams · Accepted Answer · 2020-03-25 02:43:10Z

1

    With Cht
        .ChartType = xlXYScatter
        'remove any data which might have been
        '  picked up when adding the chart
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        'add the first series
        With .SeriesCollection.NewSeries()
            .XValues = rngX.Value
            .Values = rngY.Value
        End With

        'second series
        With .SeriesCollection.NewSeries()
            .XValues = rngX.Value
            .Values = rngY.Offset(0, 7).Value
        End With

answered Mar 25, 2020 at 2:43

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

3 Comments

cena Over a year ago

i suppose the rngY have to change to yourOtherRange? Then after that when i paste my code within the while loop( Do While Application.CountA(rngY) > 0) as shown in my updated post, i got the loop without do error..

cena Over a year ago

I mean paste within the do while loop ^

cena Over a year ago

now i don't have the do while loop problem but only 1 of my chart generate the expected chart that i want while the others are empty which im not sure why..

Collectives™ on Stack Overflow

Plotting multiple data on the same chart

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related