Add multiple series to chart and plot by columns

[pp57]

Hi,

I'm new to plotting using VBAs and I'm having an issue adding multiple series to an existing chart. I think the main problem is that the data in my worksheets is in kind of a unique orientation that, due to the way that it's collected and amended, has to remain that way.

data example.JPG

Using SetSourceData I can get the first data set to plot with x-axis values in J49 and J50 and y-axis values in K49 and K50. I would like to add the other sets as well which I assume would need some type of a For loop since the number may change. I've also been looking at series collections but can't get that to work either.

This is what I have. It's mostly fluff at the moment.

With newCht
.ChartType = xlXYScatterLines
.SetSourceData Source:=ActiveSheet.Range("J49:K50"), PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = chartName
.Legend.Position = xlRight
With .Parent
.Name = chartName
End With
End With

Thanks in advance.

[NickyC]

Hi
try this macro

Please Login or Register  to view this content.

[pp57]

Awesome! This worked great. Thanks for the help.

[pp57]

I wanted to resurrect this kind of. So I've gotten the chart to plot and also added an additional line to name each trend using the value of a specific cell. It looks something like this:

Please Login or Register  to view this content.

One thing that I noticed though is that, unlike the Values and XValues, the Name value doesn't update in either the formula bar on in the chart legend without running the macro again. The cell also isn't highlighted like the ones for the values are. Is there a way to get them to update automatically?

[NickyC]

Hi
try this variation. it assumes your series names are in column A starting at row 49. Because the series are plotted by columns and each series has 2 data points it assumes the names are in every other row i.e. 49, 51 etc.

Please Login or Register  to view this content.

[pp57]

I'm still having the same issue. Here's my code as it currently stands:

Please Login or Register  to view this content.

To illustrate what the issue is, if I click on the first trend as shown in the image below: the formula bar correctly shows the name as "Thing 1" and the cells where the data originates are highlighted.

plot1.JPG

However if I change the name to "Thingzzz" and the value in C3 to 50 then trend name in the formula bar doesn't change but the line itself does.

plot2.JPG

[NickyC]

Hi
I think your problem is that you are still using

Please Login or Register  to view this content.

to set the series name. This adds the name as a string to the formula, rather than creating a link to the source cell. To have a dynamic link that changes when the source cell changes, you need to enter the name as a formula, which is what this piece of my revised code should do:

Please Login or Register  to view this content.

[pp57]

Stepped away from this for a while but with NickyC's latest comments it's working how I need it to. I have some other things that I want to add to the chart now but I'll make a new thread.