Graphing Macro


Posted by Steve on October 17, 2001 6:32 AM

Let's say that I wanted to write a macro to make some graphs for me. I recorded one with the macro recorder and I'm trying to modify it to set the labels by referencing what cell I had active when I invoked it. For example:

ActiveChart.SeriesCollection(1).XValues = "=(Data!R5C1,Data!R15C1,Data!R24C1)"

Sets the xvalues of the graph to cells, A5, A15, and A24. So what I'm having problems with is trying to do this:

ActiveChart.SeriesCollection(1).Values = "=(Data!RC,Data!R[9]C,Data!R[18]C)"

Shouldn't this set the values to the current active cell, the cell 9 rows down, and the cell 18 rows down? Also, this:

ActiveChart.SeriesCollection(1).Name = "=Data!R[-1]C"

Shouldn't this set the name of the series to the value in the cell one row to the left? I'm using Excel 97. Here is the macro,

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Data").Range("A5:V12"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=(Data!R5C1,Data!R15C1,Data!R24C1)"
ActiveChart.SeriesCollection(1).Values = "=(Data!RC,Data!R[9]C,Data!R[18]C)"
ActiveChart.SeriesCollection(1).Name = "=Data!R[-1]C"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Data"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Sales by Quarter"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "($1000's)"
End With
End Sub

Thanks for any help....



Posted by Dan on October 17, 2001 7:56 AM

Well, although I can't provide the solution right away, I can tell you the answer to your questions.
No, the way you have it entered won't do what you want. When you are setting values for your series you have it inclosed in "" which means that it literally places the text, letter for letter, of what you write in to the "Series" field. Macro4 Macro Keyboard Shortcut: Ctrl+a Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Data").Range("A5:V12"), PlotBy:= _ xlRows ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=(Data!R5C1,Data!R15C1,Data!R24C1)" ActiveChart.SeriesCollection(1).Values = "=(Data!RC,Data!R[9]C,Data!R[18]C)" ActiveChart.SeriesCollection(1).Name = "=Data!R[-1]C" ActiveChart.Location Where:=xlLocationAsObject, Name:="Data" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Sales by Quarter" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "($1000's)" End With