trying to create VBA graph using non-contiguous range

mike703 · #1 (**permalink**) 12-07-10, 12:02

Hello all, i'm in the process of trying to create a simple graph using a non-contiguous range. I thought that using the same code for another graph i've done (using a contiguous range) would work but seem to be getting unexpected results. The code I am using is below:

Code:

    ActiveSheet.Shapes.AddChart(xlColumnClustered, Left:=ActiveSheet.Range("j11").Left, Top:=ActiveSheet.Range("o11").Top, _
    Width:=ActiveSheet.Range("j11:o24").Width, Height:=ActiveSheet.Range("j11:j24").Height).Select
    With ActiveChart
    .SetSourceData Source:=ActiveSheet.Range("$c$6:$p$7,$e$1:$p$1")
    .SetElement (msoElementChartTitleAboveChart)
    .ChartTitle.Caption = "Cumulative STE Delivery"
    .Legend.Position = xlBottom
    .Legend.Left = 0.209
    .Legend.Width = 374.79
    .Legend.Height = 19.665
    .Legend.Top = 157.834
    .SeriesCollection(1).ChartType = xlLineStacked
    End With

The end result I am getting is a grpah in which it is using the data in the referenced range above as the legend data. I have tried numerous ways and methods but this is the closest i have gotten so far...

mike703 · #2 (**permalink**) 12-07-10, 18:10

Im case anybody else runs into this problem, I was able to find a solution. Not completely sure if it is the correct one, but definitely a solution. I first created the graph by hand and was able to manually manipulate the the XY and plot values. I then copied the formula the graph was using which appeared as such:

Quote:

='9A'!$C$1,'9A'!$E$1:$P$1,'9A"!$C$6:$C$7,'9A'!$E$6 :$P$7

From here I was able to break down the cell references to see how the graph was reading the formula. I then created variables for each of the 4 cell ranges in the formula. Using the UNION function, was able to combine the ranges into one statement. Code is below:

Quote:

ActiveSheet.Shapes.AddChart(xlColumnClustered, Left:=ActiveSheet.Range("j11").Left, Top:=ActiveSheet.Range("o11").Top, _
Width:=ActiveSheet.Range("j11:O24").Width, Height:=ActiveSheet.Range("j11:j24").Height).Selec t
Set Rng1 = ActiveSheet.Range("c1")
Set Rng2 = ActiveSheet.Range("e1:P1")
Set Rng3 = ActiveSheet.Range("c6:c7")
Set Rng4 = ActiveSheet.Range("e6:P7")
With ActiveChart
.SetSourceData Source:=Union(Rng1, Rng2, Rng3, Rng4)
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Caption = "Cumulative STE Delivery"
.Legend.Position = xlBottom
.Legend.Left = 0.209
.Legend.Width = 325
.Legend.Height = 24
.SeriesCollection(1).ChartType = xlLineStacked
End With

That was enough to get the desired results I was looking for. Thanks to who all took the time to read this when I did not have a clue!

Internet Services

Web Development

Digital Marketing

Consumer Tech