PDA

View Full Version : Dynamic Ranges for Charts



Maikel
09-15-2010, 12:42 AM
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
09-15-2010, 07:56 AM
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
09-16-2010, 12:52 AM
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.