Activechart.SetSourceMethod

You have headings here which are series names, two cells per series
name, plus the data below. No Category labels?

If you have category labels, you can set up a discontiguous range for
setsourcedata which will do this neatly. The range is a rectangular
region containing the names, categories, values, and the blank region
(it's gotta be blank) at the top left, where the rows of the names
intersect with the columns of the categories. This macro takes your
starting and ending row locations for the values, names, and categories,
and applies the appropriate range to the chart:

Sub SetMySourceData()
Dim mySheet As Worksheet
Dim myChart As Chart
Dim myRange As Range
Dim myRangeBlank As Range
Dim myRangeNames As Range
Dim myRangeCats As Range
Dim myRangeValues As Range
Dim NameRow1 As Integer
Dim NameRow2 As Integer
Dim CatCol1 As Integer
Dim CatCol2 As Integer
Dim DataRow1 As Integer
Dim DataCol1 As Integer
Dim DataRow2 As Integer
Dim DataCol2 As Integer

'' Dummy range definition points
'' Your code supplies them
NameRow1 = 3
NameRow2 = 4
CatCol1 = 2
CatCol2 = 3
DataRow1 = 6
DataCol1 = 6
DataRow2 = 9
DataCol2 = 8

'' Dummy object definitions
'' Your code supplies them
Set mySheet = ActiveSheet
Set myChart = mySheet.ChartObjects(1).Chart

With mySheet
'' Build the subranges
Set myRangeBlank = .Range(.Cells(NameRow1, CatCol1), _
.Cells(NameRow2, CatCol2))
Set myRangeNames = .Range(.Cells(NameRow1, DataCol1), _
.Cells(NameRow2, DataCol2))
Set myRangeCats = .Range(.Cells(DataRow1, CatCol1), _
.Cells(DataRow2, CatCol2))
Set myRangeValues = .Range(.Cells(DataRow1, DataCol1), _
.Cells(DataRow2, DataCol2))

'' Just checking
Debug.Print myRangeBlank.Address
Debug.Print myRangeNames.Address
Debug.Print myRangeCats.Address
Debug.Print myRangeValues.Address

'' Unify the range
Set myRange = Union(myRangeBlank, myRangeNames, _
myRangeCats, myRangeValues)

End With
myChart.SetSourceData Source:=myRange, PlotBy:=xlColumns

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

grinning_crow wrote:

Hi

I'm trying to write a sub-procedure that creates charts based on
variables entered from a userform. Two of the variables specify a date
range for which the graph should plot. This therefore means that I need
to use two ranges within the source parameter of the SetSourceData
method - one for the headings, and one for the data itself, which
begins a variable number of rows below the headings. As well as this,
there are a variable number of column sets which contain similar data
but for different reporting areas.

i.e. something like this:

Activechart.SetSourceData Sheet1.Range(FromColumn & "2:" & ToColumn &
"3" _ 'setting the headings from rows 2 and 3
' and then the second range grabbing the data:
, FromColumn & FromRow & ":" & ToColumn & ToRow), xlColumns

etc.

Unfortunately, when it creates the graph, its not recognising it as two
distinct ranges, but creating one graph using the entire number of rows
in the current region for the specified columns.

I've no doubt I'm being extremely dim and its right in front of me, but
if someone could point me to a solution, that would be appreciated.

Thanks.


---
Message posted from http://www.ExcelForum.com/

An alternative is to set the data range for each series separately, as
below. If you don't have categories, omit the relevant line.

Sub SetMySeriesData()
Dim mySheet As Worksheet
Dim myChart As Chart
Dim myRange As Range
Dim mySrs As Series
Dim myRangeNames As Range
Dim myRangeCats As Range
Dim myRangeValues As Range
Dim NameRow1 As Integer
Dim NameRow2 As Integer
Dim CatCol1 As Integer
Dim CatCol2 As Integer
Dim DataRow1 As Integer
Dim DataCol1 As Integer
Dim DataRow2 As Integer
Dim DataCol2 As Integer
Dim i As Integer

'' Dummy range definition points
'' Your code supplies them
NameRow1 = 3
NameRow2 = 4
CatCol1 = 2
CatCol2 = 3
DataRow1 = 6
DataCol1 = 6
DataRow2 = 9
DataCol2 = 8

'' Dummy object definitions
'' Your code supplies them
Set mySheet = ActiveSheet
Set myChart = mySheet.ChartObjects(1).Chart

With mySheet
'' Build the subranges
Set myRangeNames = .Range(.Cells(NameRow1, DataCol1), _
.Cells(NameRow2, DataCol2))
Set myRangeCats = .Range(.Cells(DataRow1, CatCol1), _
.Cells(DataRow2, CatCol2))
Set myRangeValues = .Range(.Cells(DataRow1, DataCol1), _
.Cells(DataRow2, DataCol2))

'' Just checking
Debug.Print myRangeNames.Address
Debug.Print myRangeCats.Address
Debug.Print myRangeValues.Address

End With

Do While myChart.SeriesCollection.Count 0
myChart.SeriesCollection(1).Delete
Loop

For i = 1 To myRangeValues.Columns.Count
Set mySrs = myChart.SeriesCollection.NewSeries
With mySrs
.Values = myRangeValues.Columns(i)
'' OMIT FOLLOWING LINE IF NO CATEGORIES
.XValues = myRangeCats
.Name = "=" & myRangeNames.Columns(i).Address _
(ReferenceStyle:=xlR1C1, external:=True)
End With
Next

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Jon Peltier wrote:

You have headings here which are series names, two cells per series
name, plus the data below. No Category labels?

If you have category labels, you can set up a discontiguous range for
setsourcedata which will do this neatly. The range is a rectangular
region containing the names, categories, values, and the blank region
(it's gotta be blank) at the top left, where the rows of the names
intersect with the columns of the categories. This macro takes your
starting and ending row locations for the values, names, and categories,
and applies the appropriate range to the chart:

Sub SetMySourceData()
Dim mySheet As Worksheet
Dim myChart As Chart
Dim myRange As Range
Dim myRangeBlank As Range
Dim myRangeNames As Range
Dim myRangeCats As Range
Dim myRangeValues As Range
Dim NameRow1 As Integer
Dim NameRow2 As Integer
Dim CatCol1 As Integer
Dim CatCol2 As Integer
Dim DataRow1 As Integer
Dim DataCol1 As Integer
Dim DataRow2 As Integer
Dim DataCol2 As Integer

'' Dummy range definition points
'' Your code supplies them
NameRow1 = 3
NameRow2 = 4
CatCol1 = 2
CatCol2 = 3
DataRow1 = 6
DataCol1 = 6
DataRow2 = 9
DataCol2 = 8

'' Dummy object definitions
'' Your code supplies them
Set mySheet = ActiveSheet
Set myChart = mySheet.ChartObjects(1).Chart

With mySheet
'' Build the subranges
Set myRangeBlank = .Range(.Cells(NameRow1, CatCol1), _
.Cells(NameRow2, CatCol2))
Set myRangeNames = .Range(.Cells(NameRow1, DataCol1), _
.Cells(NameRow2, DataCol2))
Set myRangeCats = .Range(.Cells(DataRow1, CatCol1), _
.Cells(DataRow2, CatCol2))
Set myRangeValues = .Range(.Cells(DataRow1, DataCol1), _
.Cells(DataRow2, DataCol2))

'' Just checking
Debug.Print myRangeBlank.Address
Debug.Print myRangeNames.Address
Debug.Print myRangeCats.Address
Debug.Print myRangeValues.Address

'' Unify the range
Set myRange = Union(myRangeBlank, myRangeNames, _
myRangeCats, myRangeValues)

End With
myChart.SetSourceData Source:=myRange, PlotBy:=xlColumns

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

grinning_crow wrote:

Hi

I'm trying to write a sub-procedure that creates charts based on
variables entered from a userform. Two of the variables specify a date
range for which the graph should plot. This therefore means that I need
to use two ranges within the source parameter of the SetSourceData
method - one for the headings, and one for the data itself, which
begins a variable number of rows below the headings. As well as this,
there are a variable number of column sets which contain similar data
but for different reporting areas.

i.e. something like this:

Activechart.SetSourceData Sheet1.Range(FromColumn & "2:" & ToColumn &
"3" _ 'setting the headings from rows 2 and 3
' and then the second range grabbing the data:
, FromColumn & FromRow & ":" & ToColumn & ToRow), xlColumns

etc.

Unfortunately, when it creates the graph, its not recognising it as two
distinct ranges, but creating one graph using the entire number of rows
in the current region for the specified columns.

I've no doubt I'm being extremely dim and its right in front of me, but
if someone could point me to a solution, that would be appreciated.

Thanks.


---
Message posted from http://www.ExcelForum.com/