John -

I slightly modified your code and got it to work with a simple data set in
A1:B22.

For the horizontal line I took advantage of the fact that you can define a
straight line by two points, begin and end. Since you need X and Y for each
point, a straingt line can be defined by 2 X's and 2 Ys. I wrote these
values to a hor line data table in range F2:G3. I then added the avg line
series with the data in F2:G3.

I used min and max of A column data to set hor line X values.

I used the B column avg to set the Y value for the hor line.

You should be able to edit this to meet your needs.

...Kelly




Sub AddAverageLine()
'Populate GrandMean values
Dim ar() As Variant
Dim avg As Double
p = ActiveChart.SeriesCollection(1).Points.Count
ReDim ar(1 To p)
' Calc avg value
avg = Application.Average(Range("b1:b22"))
avg = Application.Round(avg, 2) 'WANT THIS TO 2 DECIMALS NOTE 0

' Create Avg Line Data Table - Need Start & End X and Y values
Range("f2") = Application.WorksheetFunction.Min(Range("a1:a22"))
Range("f3") = Application.WorksheetFunction.Max(Range("a1:a22"))
Range("G2") = avg
Range("G3") = avg

' Add Avg Line to Chart
Range("F2:G3").Select
Selection.Copy
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=False, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
Application.CutCopyMode = False

End Sub
"John Michl" wrote in message
oups.com...
I'm trying to write a macro that will fill a data series based on a
single value in a spreadsheet. In this example, Series 1 is the actual
observation data. Series 2 would be the average for all points in
Series 1. My code works fine as long as the series average is rounded
to zero decimal places. If not, I receive the error "Subscript out
of Range". I've tried declaring "i" as various data types to no avail.
Help would be appreciated.


Sub AddAverageLine()

'Populate GrandMean values
Dim ar As Variant
ReDim ar(1 To p)

p = ActiveChart.SeriesCollection(1).Points.Count
i = Round(Range("GrandMean"), 0) 'WANT THIS TO 2 DECIMALS NOTE 0

For x = 1 To UBound(ar)
ar(x) = i
Next x

ActiveChart.SeriesCollection(2).Values = ar


End Sub

- John