How to merge graphs

Question

Edit:

I'm using the following code to create graphs

Dim i As Long
For i = 8 To 10

    Dim Start1 As String
    Dim End1 As String
    Dim Start2 As String
    Dim End2 As String
    Dim Start3 As String
    Dim End3 As String
    Dim Start4 As String
    Dim End4 As String

    Start1 = ThisWorkbook.Worksheets("Test Setup").Range("D" & i)
    End1 = ThisWorkbook.Worksheets("Test Setup").Range("E" & i)

    Start2 = ThisWorkbook.Worksheets("Test Setup").Range("F" & i)
    End2 = ThisWorkbook.Worksheets("Test Setup").Range("G" & i)

    Start3 = ThisWorkbook.Worksheets("Test Setup").Range("H" & i)
    End3 = ThisWorkbook.Worksheets("Test Setup").Range("I" & i)

    Start4 = ThisWorkbook.Worksheets("Test Setup").Range("J" & i)
    End4 = ThisWorkbook.Worksheets("Test Setup").Range("K" & i)

    Sheets("Data").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range(Start1 & ":" & End1)
    ActiveChart.SetSourceData Source:=Range(Start2 & ":" & End2)
    ActiveChart.SetSourceData Source:=Range(Start3 & ":" & End3)
    ActiveChart.SetSourceData Source:=Range(Start4 & ":" & End4)
    ActiveChart.Parent.Cut
    Sheets("Sheet5").Select
    ActiveSheet.Paste
    
    Next i
End Sub

Running the code give me only 3 line graphs that look like graph A

but only for the range given by this last line of code

ActiveChart.SetSourceData Source:=Range(Start4 & ":" & End4)

If I delete the lines that include Start1 through Start 3 then the graph looks like graph B instead of A.

I've tried separating the code like this

  Sheets("Data").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range(Start1 & ":" & End1)
     Sheets("Data").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range(Start2 & ":" & End2)
     Sheets("Data").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range(Start3 & ":" & End3)
     Sheets("Data").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range(Start4 & ":" & End4)

Yet I still get graphs that look like B and not graph A

The Start and End for the ranges come from here

I need help in 2 things:

I need to get graphs that look like A for all 4 ranges
I need to know if there's a way to merge the graphs while the For function is running or I need to rewrite the code in some other way THanks

Have you tried using the Macro Recorder while doing it manually? Usually this show pretty good how a code could look like. Nevertheless usually the generated code is not perfect and needs some modification (therefore I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)). — Pᴇʜ, Mar 01 '22 at 09:10
the issue I'm facing is that to choose several ranges for data the code would be ```ActiveChart.SetSourceData Source:=Range( _ "Data!$P$2:$P$9,Data!$Q$2:$Q$9,Data!$R$2:$R$9") ``` However since I'm creating the graphs separately from the loop above I can't add the next range that easily. I tried to save the Range as a whole instead of the Start and End separately but Syntax wise I can't save R1= "P" & #1 & : & "P" & #2 because when I insert Range 1 in here ActiveChart.SetSourceData Source:=Range(R1) it doesn't work — Mif987, Mar 01 '22 at 10:27
*"here `ActiveChart.SetSourceData Source:=Range(R1)` it doesn't work"*. What does not work? [*"It's not working"* is not helpful](http://idownvotedbecau.se/itsnotworking/). We need a detailed description of what did not work. — Pᴇʜ, Mar 01 '22 at 10:53
This is how I wanted to save the Range ```ThisWorkbook.Worksheets("Test Setup").Range("D" & j + 6).Value = "P" & SR & "& : &" & "P" & LR``` Which creates this P7& : &P126 - for example. If I make R1=P7& : &P126 and use this ```ActiveChart.SetSourceData Source:=Range(R1)``` I get the error Method 'Range' of object '_Global' failed — Mif987, Mar 01 '22 at 11:07
Please [edit] instead of posting code in comments, it gets pretty much unreadable. `"P" & SR & "& : &" & "P" & LR` should probably be `"P" & SR & ":P" & LR` — Pᴇʜ, Mar 01 '22 at 11:12
The correction is giving me a graph with all y values on the same x value instead of each y value on separate consecutive x values. — Mif987, Mar 01 '22 at 11:44
Can you [edit] your quesiton and add a full [mcve] with example input data? It's hard to reproduce your issue without knowing which data you are using. Best if you can show a screenshot of how you want it to look like and what the outcome of your code looked like. — Pᴇʜ, Mar 01 '22 at 15:44
The data column that is named "Cycle" is which column letter? The screenshot doesn't show. — Pᴇʜ, Mar 02 '22 at 10:26
Well your columns D … K contain text not numbers. So how would this be shown in a x/y graph. Mathematics only works with numbers. Texts like "P125" cannot be shown in a xy-graph. — Pᴇʜ, Mar 03 '22 at 12:29
Correct, those text are used to define the cells that will define the range (Start4 - End4) here ```ActiveChart.SetSourceData Source:=Range(Start4 & ":" & End4)``` I am getting graphs (as I posted in the main question) but not the correct amount or with a range- as explained in the main post. — Mif987, Mar 03 '22 at 12:38
But data like `P125` and `Q569` **cannot** be shown in a xy graph, this is just not possible. — Pᴇʜ, Mar 03 '22 at 12:42
To generate a graph you can only use numbers and you need to specify your source at once like `ActiveChart.SetSourceData Source:=Range("$A$7:$K$17")` you cannot have multiple `ActiveChart.SetSourceData`. — Pᴇʜ, Mar 03 '22 at 12:46
But ```P125``` and ```Q569``` are going to the the cell names that will define the range, not the data points. Technically what I want the code to do is the look for the cell ranges in "TestSetup" ( that are in columns D through K) and then extract the data from sheet "Data" using the cell ranges defined in "Test Setup" — Mif987, Mar 03 '22 at 12:50
The thing is that I am getting graphs and the correct type with ``` Sheets("Data").Select ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select ActiveChart.SetSourceData Source:=Range(Start1 & ":" & End1) ActiveChart.SetSourceData Source:=Range(Start2 & ":" & End2) ActiveChart.SetSourceData Source:=Range(Start3 & ":" & End3) ActiveChart.SetSourceData Source:=Range(Start4 & ":" & End4) ``` Only for the Start 4 and End4 range but if I remove ranges 1-3 I get a wrong graph — Mif987, Mar 03 '22 at 12:51
Again this is impossible with the data you show because it contains **letters**. Also if you have multiple `.SetSourceData` like you have 4 of them then the last one overwrites the first 3 so whatever you get as a result **must be wrong**. You need to have everything in one single `.SetSourceData` statement! — Pᴇʜ, Mar 03 '22 at 13:13
This is the code that I get when Recording the macro ``` ActiveChart.SetSourceData Source:=Range("Data!$P$7:$P$126")``` The range is defined with letters and numbers because they reference the cells where I'm getting the data. I'm just changing specific cells for a variable (Startn and Endn) since the range I need to use changes with the for loop. — Mif987, Mar 03 '22 at 14:23
Oh my god, now I get it the data you show is not the data. It is the addresses to the data. Correct? • Well in this case you need to ensure that the addresses include those `$` So change `P125` to `$P$125` to make it work properly. Nevertheless you can only have one `.SetSourceData` per chart because this overwrites the entire data source. If you use `.SetSourceData` twice on one chart the first data source is overwritten and lost. — Pᴇʜ, Mar 03 '22 at 14:43

score 0 · Answer 1 · answered Mar 03 '22 at 14:47

0

Make sure your addresses have $ so change the data that are addresses from P125 to $P$125.
Make sure you have only one .SetSourceData per chart. If you use multiple on one chart the entire data source gets overwritten and the first is lost/useless.

If you need to add multiple ranges to your data source combine them like:

.SetSourceData Source:=Range("$P$7:$P$126,$Q$7:$Q$126")

or use union

.SetSourceData Source:=Union(Range("$P$7:$P$126"), Range("$Q$7:$Q$126"))

answered Mar 03 '22 at 14:47

Pᴇʜ

56,719
10
49
73

Although I am getting graphs, they're still like graph B in the picture and not graph A, for some reason the code isn't recognizing the Start1:End1 Range. the only time I do get the graph correctly is when I write the code incorrectly as written in the post – Mif987 Mar 06 '22 at 06:56
Also, the issue with the ranges in my case is that I do not know how many ranges I'll have, it changes depending on the data, that's why I need to find a way to do it dynamically. – Mif987 Mar 06 '22 at 10:46

How to merge graphs

1 Answers1