How to allign stacked charts

i wrote this macro to create a chart with multipul axes and it works
great. then i tried to run this as a subroutine using a for loop and i
get 97 graphs (just like i wanted). the problems come in when all the
graphs are stacked one ontop of the other. i would like them to line
up two per row. however i have been unsuccussful in figuring out how.
please help

here is my code.

Private Sub Grapher(i As Integer)

Dim cColumn As Integer
cColumn = i
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Sheet1.Range(Sheet1.Cells(4, cColumn), Sheet1.Cells(8,
cColumn))
Set rng2 = Sheet1.Range(Sheet1.Cells(17, cColumn),
Sheet1.Cells(21, cColumn))

Charts.Add
ActiveChart.ChartType = xlColumnClustered
'ActiveChart.SetSourceData Source:=Sheets("06-15-04
Data").Range("B1:B23"), _
' PlotBy:=xlColumns
'ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
'labels
ActiveChart.SeriesCollection(1).XValues = _
"=('06-15-04 Data'!R4C1,'06-15-04 Data'!R5C1,'06-15-04
Data'!R6C1,'06-15-04 Data'!R7C1,'06-15-04 Data'!R9C1)"
'data series
ActiveChart.SeriesCollection(1).Values = rng1
ActiveChart.SeriesCollection(2).Values = rng2
'end data series

ActiveChart.SeriesCollection(2).AxisGroup = 2

With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
HasLeaderLines:=False, ShowSeriesName:=False,
ShowCategoryName:=False, _
ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(2).DataLabels.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Position = xlLabelPositionInsideBase
.Orientation = xlDownward
End With
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Delete

Dim width As Integer
Dim height As Integer
'width = width of chart
'height = height + height of chart

ActiveChart.Location Whe=xlLocationAsObject, Name:="Grapher"
width = ActiveChart.ChartArea.width
height = ActiveChart.ChartArea.height

'With Worksheets("Grapher").ChartObjects(i)
'.Width = 3
'.Height = 3
'.Left = 0
'.Top = 0
'End With



ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Caption = Sheet1.Cells(2, cColumn)
End Sub

Hi David,

Check out Jon's page on moving and size chartobjects.
http://peltiertech.com/Excel/ChartsH...oveAChart.html

Cheers
Andy

david wrote:

i wrote this macro to create a chart with multipul axes and it works
great. then i tried to run this as a subroutine using a for loop and i
get 97 graphs (just like i wanted). the problems come in when all the
graphs are stacked one ontop of the other. i would like them to line
up two per row. however i have been unsuccussful in figuring out how.
please help

here is my code.

Private Sub Grapher(i As Integer)

Dim cColumn As Integer
cColumn = i
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Sheet1.Range(Sheet1.Cells(4, cColumn), Sheet1.Cells(8,
cColumn))
Set rng2 = Sheet1.Range(Sheet1.Cells(17, cColumn),
Sheet1.Cells(21, cColumn))

Charts.Add
ActiveChart.ChartType = xlColumnClustered
'ActiveChart.SetSourceData Source:=Sheets("06-15-04
Data").Range("B1:B23"), _
' PlotBy:=xlColumns
'ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
'labels
ActiveChart.SeriesCollection(1).XValues = _
"=('06-15-04 Data'!R4C1,'06-15-04 Data'!R5C1,'06-15-04
Data'!R6C1,'06-15-04 Data'!R7C1,'06-15-04 Data'!R9C1)"
'data series
ActiveChart.SeriesCollection(1).Values = rng1
ActiveChart.SeriesCollection(2).Values = rng2
'end data series

ActiveChart.SeriesCollection(2).AxisGroup = 2

With Selection.Interior
.ColorIndex = 17
.Pattern = xlSolid
End With

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
HasLeaderLines:=False, ShowSeriesName:=False,
ShowCategoryName:=False, _
ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(2).DataLabels.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Position = xlLabelPositionInsideBase
.Orientation = xlDownward
End With
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Delete

Dim width As Integer
Dim height As Integer
'width = width of chart
'height = height + height of chart

ActiveChart.Location Whe=xlLocationAsObject, Name:="Grapher"
width = ActiveChart.ChartArea.width
height = ActiveChart.ChartArea.height

'With Worksheets("Grapher").ChartObjects(i)
'.Width = 3
'.Height = 3
'.Left = 0
'.Top = 0
'End With



ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Caption = Sheet1.Cells(2, cColumn)
End Sub

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info