Embedded Chart with data source in different columns

[irki]

Hi all,

I trying to create an embedded chart for which the data are in two non adjacent
columns. this is how I set my data:

Set datarng = Range("F8:F27", "I8:I27")

That is for this function Y = f(X), my X= "F8:F27" and my Y = "I8:I27" which are not adjacent. but there are data in the columns G,H and J as well.
Then to plot I have:

With cht
    .Type = xlXYScatter
    .SetSourceData Source:=datarng, PlotBy:=xlColumns
End With

Unfortunately I'have a plot with other data series in columns G and H as well but not J as it happen is after column I. Then I want to add on the same chart another graph.
Questions:
1) How do you set data source from non adjacent columns?
2) Why do I have th plot of G = f(F), J = f(F) as well when I just want to chart I = f(F)?
3) How do I then add the graph for J = f(F) on the same chart?

Thank you

 

[gehusi]

Sounds like dynamic charting would work better for you than VBA. Can you post a sample file?

 

[irki]

This is the whole code. Just remember that I have data in columns F,G,H,I,J and try to chart I = f(F), and then J = f(J).

Sub IrkiChart()
'Charts the approximate and the exact solutions of a SDE
Dim chtobj As ChartObject, cht As Chart
Dim datarng As Range, obj As Object

Application.ScreenUpdating = False
Set datarng = Range("F8:F27", "I8:I27")

On Error Resume Next
Sheet1.ChartObjects.Delete
Set chtobj = Sheets("Sheet1").ChartObjects.Add(1, 1, 1, 1)
Set cht = chtobj.Chart
With chtobj
    .Top = Range("M7").Top
    .Left = Range("M7").Left
    .Height = 150
    .Width = 450
    .RoundedCorners = True
End With

With cht
    .Type = xlXYScatter
    .SetSourceData Source:=datarng, PlotBy:=xlColumns 'plot F = f(I)

    .HasLegend = True
    With .PlotArea.Fill
        .ForeColor.SchemeColor = 50
        .BackColor.SchemeColor = 43
        .TwoColorGradient Style:=msoGradientHorizontal, Variant:=1
    End With
End With
   Application.ScreenUpdating = True
End Sub

Dynamic charting sound like the right think as a quick googling can tell.

Cheers

 

[gehusi]

To clarify, you want to be able to switch between various sets of Y value data in an XY scatter plot. Do I have that right? And the X values are remaining constant?

 

[irki]

Yes! but given the data in columns F, G, H, I, J, I want the (X,Y)-scatter plot of (F, I) and (F, J). but my plot is coming out with (F,G), (F,H) as well. This happen when I tried to chart (F,I) only and I will still need the method to add (F,J) afterward.