John:

I made up a smalls data set of A1:B22 and modified your code to add a
horizontal line.

I computed the avg, and then developed a 4 cell horizontal line data table
(F2:G3) to store the start and End X and Y's for horizontal line. You only
need 2 points to establish a straight line, so why add avg to every row of
data?

Here's my code. Let me know if it solves your problem.


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