Creating a chart from multiple series with the same Y value

Question

I need to create a chart to show coverage by different people over time. For example, a scatter chart with no markers showing with one series for each time period that someone will be available. Each person has a different Y value (e.g. Joe=1, Pat=2, Kevin=3). The purpose is to visualize gaps in coverage where time is the X axis.

I’ve found how to add each series through…

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = “=Sheet1!$A$1:$B$1”
    ActiveChart.SeriesCollection(1).Values = “=Sheet1!$C$1:$D$1”
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).XValues = “=Sheet1!$A$2:$B$2”
    ActiveChart.SeriesCollection(2).Values = “=Sheet1!$C$2:$D$2”

…and so on, but that code was generated by manually assigning X and Y values.

What I need to do is:

1) have all the X and Y values brought in (plotted on the chart) dynamically based on the number of rows on the worksheet (the number of rows will not always be the same), and

2) format all the series with the same Y value in the same way (e.g. all series where Y=2 are red lines, and all series where Y=1 are blue lines).

I was thinking that I could use a loop using i for iteration/row number until there's an empty cell, but I've been unable to make that work in this instance.

The next step is being able to add data from multiple worksheets on the same chart. I found some code that went like this...

    Dim ws As String
    For Each ws in ThisWorkbook

Will that take data from all worksheets?

I'd like to post an example of what I'm trying to do, but apparently I need rep first. Here is a link. The only difference is that I want everything on the same line to be the same color (i.e. on the graph with only 2 objects in the legend). http://i280.photobucket.com/albums/kk185/navytoolmanshortfieldbreak/Graph%20Problem%201_zpszxpsgikn.jpg

Update: If you can't get to the photo, there are 4 rows and 4 columns of data. Each row contains a series that should be plotted as a horizontal line. The first two columns are the X coords, while the last two columns are the Y coords. The first row shows a line from 1,1 to 3,1, the second row from 5,1 to 7,1, etc.

    1    3    1    1
    5    7    1    1
    2    4    2    2
    7   10    2    2

BigBobby BigBobby · Accepted Answer · 2015-05-18T16:10:53

Answers to 1 and 2) This code will loop through your data, and plot it as you've described:

' Select Nothing so that Excel doesn't "help" by adding its own series
Range(Cells(ActiveSheet.UsedRange.Rows.Count + 1, ActiveSheet.UsedRange.Columns.Count + 1), _
      Cells(ActiveSheet.UsedRange.Rows.Count + 2, ActiveSheet.UsedRange.Columns.Count + 2)).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select

' Add all of the desired series.
Row = 1
While Cells(Row, 1).Value <> ""
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(Row).Name = "Series" & Row
    ActiveChart.FullSeriesCollection(Row).XValues = "=Sheet1!$A$" & Row & ":$B$" & Row
    ActiveChart.FullSeriesCollection(Row).Values = "=Sheet1!$C$" & Row & ":$D$" & Row
    Row = Row + 1
Wend

As an answer to your unofficial 3) This code will loop through all of the worksheets in your workbook:

Dim ws As Worksheet
For Each ws In ThisWorkbook
Next ws

Creating a chart from multiple series with the same Y value

1 Answers