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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.