Dynamic range in graphing macro

  • Thread starter Thread starter Colin Macleod
  • Start date Start date
C

Colin Macleod

I'm trying to write a macro which will create a line graph and have included
the following line:

ActiveChart.SetSourceData
Source:=Sheets("Data").Range("DynDates,DynBrandVol,DynCatVol"), _
PlotBy:=xlColumns

where DynDates, DynBrandVol and DynCatVol are dynamic ranges defined using
the offset function. This throws up an error message, and I seem to remember
seeing something about not being able to use dynamic ranges in this way. Am
I right - and what's the way round it?

Thanks for any help.

Colin Macleod
 
I thinkn the problem is not that you are using dynamic
ranges, but that you use ranges illigally.

It appears that you want to use 3 ranges for your chart;
in this case

Sub Update_Chart()
Dim my Chart as chart


Redefine_Source "XValues"
Redefine_Source "Values"

With Chart.SeriesCollection("Results")
.XValues = Range("lstChart_Systems")
.Values = Range("lstChart_Results")
End With

Format_Chart
End Sub



-----Original Message-----
I'm trying to write a macro which will create a line graph and have included
the following line:

ActiveChart.SetSourceData
Source:=Sheets("Data").Range
Click to expand...
("DynDates,DynBrandVol,DynCatVol"), _
 
Sorry posted incomplete message earlier by mistake

I think you are using Range illegally. If you have several dynamic ranges,
you should use SeriesCollection method for Charts to assign ranges, one by
one for each range. The way I do it:

1) first, name your data sources in the spreadsheet (for example, "XValues",
"YValues1", "YValues2", etc).
2) Each time chart is activated, update the names using simple sub
Update_Name
2) Assign names to your chart (see code below)

It should work for any chart with any number of data series.
Good luck -

RADO


Sub Update_Chart()
Dim myChart As Chart

'refer to your chart (assuming it's called "MyChart")
Set myChart = Charts("MyChart")

'Update your data sources first using sub update_name (see below)
Update_Name "DynDates"
Update_Name "DynBrandVol"
Update_Name "DynCatVol"

' Assign new values
With myChart
'first, assign X Values (assuming you want dates to be X values).
You have to do only once.
.SeriesCollection(1).XValues = Range("DynDates")

'then, assign Y Values for each series (assuming you have series 1
and 2, you can have as many as you want)
.SeriesCollection(1).Values = Range("DynBrandVol")
.SeriesCollection(2).Values = Range("DynCatVol")
End With
End Sub

Sub Update_Name(myName As String)
'redefines named range
'by looping through the range until the first empty cell
' assuming data is stored vertically (from top to bottom)

Dim First_Cell As Range
Dim Last_Cell As Range
Dim Next_Cell As Range

Set First_Cell = Range(List).Cells(1, 1)
Set Next_Cell = First_Cell.Offset(1, 0)

Do Until Next_Cell = Empty
Set Next_Cell = Next_Cell.Offset(1, 0)
Loop

Set Last_Cell = Next_Cell
Names(myName).RefersTo = Range(First_Cell, Last_Cell)
End Sub

Finally, how to call the sub "Update_Chart" - I usually assign it to the
event Chart_Activate (in your VB editor, select your chart module, view
code, and select Chart_Activate event. Then use this code:

Sub Chart_Activate()
Update_Chart
End Sub
 
Back
Top