Adding more series to a XY scatter chart

[Graham Whitehead]

Hi, I am trying to make a XY scatter chart with Y error bars for each entry.
I have the code to collect three series for the chart, and also to draw the
chart and add the first series to it. However, I am not completely sure how
to add the next two series to the chart. If anyone could take a look at the
code and give me any suggestions I would be extremely gratefull. Thanks.

With ActiveSheet
lngLastRow1 = Sheets("Sheet2").Range("H65536").End(xlUp).Row
lngLastRow2 = Sheets("Sheet2").Range("I65536").End(xlUp).Row
lngLastRow3 = Sheets("Sheet2").Range("J65536").End(xlUp).Row
Set rngYValues1 = Sheets("Sheet2").Range("H1:H" & lngLastRow1)
Set rngXValues1 = Sheets("Sheet2").Range("B1:B" & lngLastRow1)
Set rngYValues2 = Sheets("Sheet2").Range("I" & lngLastRow1 + 1 &
":I" & lngLastRow2)
Set rngXValues2 = Sheets("Sheet2").Range("B" & lngLastRow1 + 1 &
":B" & lngLastRow2)
Set rngYValues3 = Sheets("Sheet2").Range("J" & lngLastRow2 + 1 &
":J" & lngLastRow3)
Set rngXValues3 = Sheets("Sheet2").Range("B" & lngLastRow2 + 1 &
":B" & lngLastRow3)
Set chtChart = .ChartObjects.Add(74, 68, 578, 293).Chart
End With

With chtChart
.ChartType = xlXYScatter
.SetSourceData rngYValues1, PlotBy:=xlColumns
.Axes(xlValue).MajorGridlines.Delete
.HasLegend = False
With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = UBound(arrScore) + 2
End With
With .Axes(xlCategory)
.MinimumScale = arrScore(LBound(arrScore)) - 2
.MaximumScale = arrScore(UBound(arrScore)) + 2
.TickLabels.NumberFormat = "0.00"
End With
With .SeriesCollection(1)
.XValues = rngXValues1
.ErrorBar Direction:=xlY, Include:=xlMinusValues,
Type:=xlCustom, MinusValues:="={100}"
End With
With .ChartGroups(1)
.Overlap = 0
.GapWidth = 10
.HasSeriesLines = False
.VaryByCategories = False
End With
End With

 

[Jon Peltier]

Graham -

You'll find some VBA charting techniques on this web page:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon