Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005
    Posts
    25

    Unanswered: trying to create VBA graph using non-contiguous range

    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...

  2. #2
    Join Date
    Mar 2005
    Posts
    25
    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:

    ='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:

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •