Dynamic Ranges for Charts

[Maikel]

Hey all,

i've looked everywhere for a solution for this but haven't found one yet.

I'm looking for a way to use a dynamic range for the data which should be used for a chart. I still can't get the .values property assigned in such a way that it works. I already have the needed column number (col As Long, colRef as String) And the rownumbers are fixed (16 & 18).

How should i assign the .Values property so it will use the combined data of column col (long) or colref (string)?
And more in general how to use the .Values property of a chart for any datarange with dynamic row (long) , col (long) ranges (multiple cells ; in a row/column or different combined ranges)

* part of code

With ActiveChart.SeriesCollection.NewSeries
   .Name = Sheets("Data").Range("A16")
   .Values = Sheets("Data").Range(colRef, "16", colRef, "18").Value
   ...
End With

Many thanks in advance,

[Kenneth Hobs]

Maybe this will give you some ideas.

Sub Macro1()
    Dim outstanding As Range
    Dim backlog As Range
Set outstanding = Sheet1.Range(Cells(2, 1), Cells(11, 1))
    Set backlog = Sheet1.Range(Cells(2, 2), Cells(11, 2))
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).Values = outstanding
    ActiveChart.SeriesCollection(2).Values = backlog
End Sub

Sub Macro2()
    Dim outstanding As Range
    Dim backlog As Range
    Range("A1").Select 'Make sure that no object is selected.
    Set outstanding = ActiveSheet.Range("A2", Range("A" & Rows.Count).End(xlUp))
    Set backlog = ActiveSheet.Range("B2", Range("B" & Rows.Count).End(xlUp))
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!" & outstanding.Address(True, True, xlR1C1)
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!" & backlog.Address(True, True, xlR1C1)
    Range("A1").Select
End Sub

[Maikel]

Maybe this will give you some ideas.

Sub Macro1()
Dim outstanding As Range
Dim backlog As Range
Set outstanding = Sheet1.Range(Cells(2, 1), Cells(11, 1))
Set backlog = Sheet1.Range(Cells(2, 2), Cells(11, 2))
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = outstanding
ActiveChart.SeriesCollection(2).Values = backlog
End Sub
 
Sub Macro2()
Dim outstanding As Range
Dim backlog As Range
Range("A1").Select 'Make sure that no object is selected.
Set outstanding = ActiveSheet.Range("A2", Range("A" & Rows.Count).End(xlUp))
Set backlog = ActiveSheet.Range("B2", Range("B" & Rows.Count).End(xlUp))
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!" & outstanding.Address(True, True, xlR1C1)
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Values = "=Sheet1!" & backlog.Address(True, True, xlR1C1)
Range("A1").Select
End Sub

Thank you for your reply i was able to solve what i was looking for.

I still have one last question though. I think it might be obvious for you but considering my experience yet it's not for me.

Why do the following 2 examples differ. (The second version doesn't set a range)

#1

Set seriesRange = Sheet2.Range(Cells(16, col), Cells(18, col))
Sheets("Output").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
With ActiveChart.SeriesCollection.NewSeries
   .Name = Sheet2.Range("A16")
   .Values = seriesRange
   .XValues = Sheet2.Range("A16:A18")
   .ApplyDataLabels
    End With

#2
    Sheets("Output").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
Set seriesRange = Sheet2.Range(Cells(16, col), Cells(18, col))
With ActiveChart.SeriesCollection.NewSeries
   .Name = Sheet2.Range("A16")
   .Values = seriesRange
   .XValues = Sheet2.Range("A16:A18")
   .ApplyDataLabels
    End With

Why does the seriesRange appear empty in #2 and not in #1.
Hoping you can answer this last question in regard to your response.