Add multiple data series to excel chart with VBA

Go To StackoverFlow.com

4

Assuming I have my data in rows and I want to create a chart (x,y scatter) from it.

date    1.1. 1.2. 1.3. 1.4 1.5
set1    12  36  12  23  33
set2    44  22  11  1   13
set3    54  5   56  56  34
set4    1   2   6   12  33

how can I add set1,set2 and set4 but not set3 to the chart without having to select them individually one after another?

For the x-range (date) I can do

Dim xrange As Range
Set xrange = Application.InputBox("select x-range", "Get Range", Type:=8)

But doing the same for the data sets (selection of multiple rows) won't work because each series in a scatter plot requires a individual range

   ActiveChart.SeriesCollection(1).XValues = xrange
   ActiveChart.SeriesCollection(1).Values = "=Sheet1!$4:$4"

but I have a selection of multiple datasets. I would need a way to split the ranges from my input box selection(s) and to write them to unique variables. Then I would add a new series for each xrange + variable pair with the above method.

Is there a wayto do this?

A potentail answer does not need to stick to what I posted above, any suggestions are welcome.

2012-04-03 22:54
by Martin H
You have to use .SeriesCollection.NewSeries. See my reply in http://stackoverflow.com/questions/9778827/graphs-with-various-y-values-and-one-x-values-in-excel-vba - Siddharth Rout 2012-04-03 23:03


4

Set the source data range. Excel will automatically create the series for you. In the example below I have column A as X, column B as Y1 and column D as Y2:

ActiveChart.SetSourceData Source:=Range("A1:B4,D1:D4")
2012-04-03 23:56
by bouvierr
Delete the label ("Date" in this case) in the cell atop the X values, to help Excel recognize that they are X values and not another set of Y values. This might be unnecessary if Excel notices date formats in the first column, but it's better to be sure - Jon Peltier 2013-10-13 21:00