loop through rows, creating a graph for each row

[snazster]

Hi,

I need help please looping through rows on a huge spreadsheet and creating a graph for each row (actually part of a row).

I've created a macro to do this for one row, then edited the macro to do the job for 8 or 9 rows by coping and pasting the original macro code.

There must be a way to say:
read the first row
create a graph
read the second row
create a graph

Can anyone help this newbie?

 

[snazster]

If it helps, here is a copy of the code (note that there are three series per graph, and all three series are taken from one line):


Sub AddChart()
'
' AddChart Macro
' Macro recorded 03/07/2011 by Derek
'

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:B2,D2,E2,G2,H2") _
, PlotBy:=xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "={""End of Year""}"
ActiveChart.SeriesCollection(2).XValues = "={""End of Year""}"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R2C9:R2C12"
ActiveChart.SeriesCollection(2).Name = "=""Target"""
ActiveChart.SeriesCollection(3).XValues = "={""End of Year""}"
ActiveChart.SeriesCollection(3).Values = "=Sheet1!R2C13:R2C16"
ActiveChart.SeriesCollection(3).Name = "=""National Expectation"""
ActiveChart.SeriesCollection(1).XValues = ""
ActiveChart.SeriesCollection(2).XValues = ""
ActiveChart.SeriesCollection(3).XValues = ""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Writing"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "End of Year"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Points"
End With
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 9
.Shadow = False
End With
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 9
.Shadow = False
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, _
Degree:=0.231372549019608
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 2
End With
ActiveChart.ChartArea.Select









Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:B3,D3,E3,G3,H3") _
, PlotBy:=xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "={""End of Year""}"
ActiveChart.SeriesCollection(2).XValues = "={""End of Year""}"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R3C9:R3C12"
ActiveChart.SeriesCollection(2).Name = "=""Target"""
ActiveChart.SeriesCollection(3).XValues = "={""End of Year""}"
ActiveChart.SeriesCollection(3).Values = "=Sheet1!R3C13:R3C16"
ActiveChart.SeriesCollection(3).Name = "=""National Expectation"""
ActiveChart.SeriesCollection(1).XValues = ""
ActiveChart.SeriesCollection(2).XValues = ""
ActiveChart.SeriesCollection(3).XValues = ""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Writing"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "End of Year"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Points"
End With
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 9
.Shadow = False
End With
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 9
.Shadow = False
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, _
Degree:=0.231372549019608
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 2
End With
ActiveChart.ChartArea.Select



End Sub

Thanks

 

[snazster]

Does the silence mean this is not possible?

 

[Weaver]

To be fair, I'm guessing the 'silence' may be down to the fact that something like this isn't easy to do without a set of appropriate test data. What I can tell you is that it certainly is possible.

Say you wanted to do this for rows 2 to 10

At either end of the code for just 1 graph,

for i = 1 to 10
'your code here
next i

then replace

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:B2,D2,E2,G2,H2") _
        , PlotBy:=xlRows

with

r=replace("Ax:Bx,Dx,Ex,Gx,Hx","x","2")
Source:=Sheets("Sheet1").Range(r) _
         , PlotBy:=xlRows

and

ActiveChart.SeriesCollection(3).Values = "=Sheet1!R2C13:R2C16"

with

r="=Sheet1!RxC13:RxC16"
ActiveChart.SeriesCollection(3).Values = r

I think that ought to work

 

[snazster]

Hi,

Thanks for that.

The first part works fine and it loops through and creates 10 graphs, but they all contain the same data because the second part

r=replace("Ax:Bx,Dx,Ex,Gx,Hx","x","2")
Source:=Sheets("Sheet1").Range(r) _
         , PlotBy:=xlRows

creates an error (compile error/syntax error).

Your time to comment on this is much appreciated, thanks.

 

[Weaver]

Sorry, should be

r=replace("Ax:Bx,Dx,Ex,Gx,Hx","x",i)

I looked at the code I posted and I missed something else

r = "=Sheet1!RxC13:RxC16"
ActiveChart.SeriesCollection(3).Values = r

should be

r = replace("=Sheet1!RxC13:RxC16","x",i)
ActiveChart.SeriesCollection(3).Values = r