HOW TO: Combine 1 dynamic graph with another static

I'm plotting columns "O" and "P" using the code below and columns "Q" and "R" is just automatic(?); meaning I use no code for that.

I don't know how you expect the Q:R data series to automatically show up without telling Excel to include it. As written, it appears to me that only the O:P data series will be included in the chart. In order to add the Q:R data series, I would expect to add that series separately using the seriescollection object. Perhaps the SeriesCollection.Add method or the SeriesCollection.NewSeries method (followed by code to correctly define the new series) https://docs.microsoft.com/en-us/off...riescollection Perhaps something like:

Please Login or Register  to view this content.

How can I join these two lines in the same graph?

Do I understand correctly, you want to connect the last point of the O:P data series with the first point of the Q:R data series? If so, I see three choices:
1) If you want the line to be formatted like the O:P data series, copy Q3:R3 and paste a copy of those values into O153:P153 (if I counted the row numbers correctly). Be sure that the series definition in the chart includes this additional row of data.
2) If you want the line to be formatted like the Q:R data series, copy O152:P152 and insert those values into Q3:R3 (shifting the current values down one row). Be sure that the series definition in the chart includes this additional row of data.
3) If you want the line to be formatted differently, make copies O152:P152 and Q3:R3 in a convenient block of cells, then add those cells as a third data series.

I'd probably do something like that. Did I understand your objectives correctly?

Hey Mr. Shorty,

I tried copying your suggested code but I got an error.

You understood exactly what I want to do with one exception. The number of iterations on column O is variable. Meaning that I can't simply copy the values Q:R and past them below the last values of O:P. My macro clears the columns O3:P completely before generating all those values based on an input (150 in this case).

So that you can see what I really want to do, this is what I have:
graphs.PNG
One graph for the columns O:P, one for Q:R

And I would like, below the second graph, to introduce a third one which would simply be the combination of the two (the complete graph).

Ps. I didnt paste here the full macro I have because I didn't want to put more information that was needed but in case you do need it, here you go:

Please Login or Register  to view this content.

Thank you for your help!

I'm not sure what error(s) you are getting. The code worked for me yesterday. Do you understand the idea (even if there is something wrong with the specific syntax presented)? The idea is to use either the seriescollection.add or seriescollection.newseries methods to add series to the chart.

The number of iterations on column O is variable. Meaning that I can't simply copy the values Q:R and past them below the last values of O:P.

I'm not sure I understand this restriction. I see no programmatic reason why your macro could not append one or more entries from Q:R to the O:P columns, so this limitation must be something you are imposing on the code/spreadsheet that I don't understand. Programmatically, something like:

Please Login or Register  to view this content.

Should append the first Q:R entry at the end of the O:P set of data. You have suggested that this would be disallowed, though, but I am not sure why this is disallowed.

On further consideration, If you don't need the two sections of line formatted differently, appending all of the Q:R data onto the end of the O:P data might be the easiest overall way to do this. It will avoid the need to add a second data series to the chart, which will simplify chart creation. Why are you disallowed from appending the Q:R data onto the O:P data? If it's just about "O:P is variable length", good programming structure should allow the macro to keep track of where the O:P data ends and the Q:R data begins, so that should not prevent appending the two data sets into one.

Hi again,

I can't seem to make it work with the .SeriesCollection function, I must be doing something wrong.

Anyhow, I'm trying it this way:

Please Login or Register  to view this content.

Which gives me this result:
chart1.PNG

-The first series was plotted correctly (in blue).
-Regarding the second series, it appears that the x values are being plotted as the y values in the orange line, and the y values are correctly shown in the gray line.

I remind you that these are the columns I'm trying to plot:
table.png

Any ideas on how I should proceed? Should I just go back to the .SeriesCollection method?

I appreciate your help,
Filipe F

When you use give an Excel chart (whether manually or through VBA) a 4 column source data range, it's inclination is to use the first (left) column as the x values, and the other three columns as three y values series.

The main advantage to this approach is that, because we are having trouble figuring out how to use the SeriesCollection.Add or SeriesCollection.NewSeries methods, we no longer need to add a data series to the chart. Excel has given us 3 data series to work with, so we need only edit the existing data series until they represent the correct values. We can use the .Xvalues and .values properties to assign the appropriate ranges to one data series, and use the .Delete method to remove the extra data series.
https://docs.microsoft.com/en-us/off...series.xvalues
https://docs.microsoft.com/en-us/off....series.values

Please Login or Register  to view this content.

Hi,

I get bugs on both lines:
1.PNG
and
2.PNG

Curious. both of those statements worked just fine for me. What error did you get? What debugging did you try? Is there a sheet called "AidCalc1" in the workbook that contains the code? Are there 3 or more data series in the active chart (add a watch for "activechart.seriescollection.count")? What's the value in Lastrow5 (from the locals window)?

Bugs aside, do you understand conceptually what I am trying to get the code to do? Are you understanding the idea of the SeriesCollection object and how to use the various properties and methods associated with it?

Since about half of programming is debugging, it should be useful to become familiar with VBA's debugging tools: http://www.cpearson.com/Excel/DebuggingVBA.aspx