Putting the series 1 and series 2 titles from a worksheet

[Jeffrey Marks]

This is the code I have to use 2 rows in a worksheet to create a clustered column chart. I'm currently getting an error with the series collection 2 name.

Any help would be greatly appreciated!!

Jeff


Sub TwoRowsBarChart()

Dim i As Integer, shName As String
Dim rowData As Range, rowXAxis As Range
Dim chtTitle As Range
Dim series1Title As Range

' *** CUSTOMIZE ***
shName = "March_20052006_OGT Query"
Set rowData = Sheets(shName).Range("f2:k3")
Set rowXAxis = Sheets(shName).Range("f1:k1")
Set chtTitle = Sheets(shName).Range("d2")
Set series1Title = Sheets(shName).Range("b2")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'
'
'
'
'
For i = 0 To 84
Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=rowData.Offset(i, 0), _
PlotBy:=xlRows
.SeriesCollection(1).XValues = rowXAxis
.HasLegend = False
.HasTitle = True
.ChartTitle.Characters.Text = chtTitle.Offset(i, 0)
.SeriesCollection(1).Name = series1Title.Offset(i, 0)
.SeriesCollection(2).Name = series1Title.Offset(i + 1, 0)
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Percent Passing"
With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 1
.MinorUnit = 0.1
.MajorUnit = 0.2
.TickLabels.NumberFormat = "0%"
End With

' *** NOTE ***
' I cannot test the following.
' Change #If 0 to #If 1.
' If it works for you, remove #If and #End
' directives
#If 0 Then
.SetElement (msoElementPrimaryValueAxisTitleVertical)
.SetElement (msoElementChartTitleAboveChart)
#End If
End With
ActiveChart.Deselect
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

'
Sheets(shName).Activate
Range("a1").Activate

End Sub