Writing data to chart/graph with a variable data range

[Dav1000]

My latest VBA endevor involves processing an array of data were the data may be 150 rows long or perhaps 1500 rows. The processing or calculating part of my macro works fine using a For Next loop that is controlled by this statement.

[vba]n = Worksheets("Output").Range("C:C").Cells.SpecialCells(xlCellTypeConstants).C ount

For i = 1 To n[/vba]



The second part of this work book(controlled by a button in the worksheet) writes this data to a scatter plot. I need VBA to recognize that the incomming data set can/will have a different range every time it is run. Does anyone here have any ideas how to accomplish this?

As an example one data series in the chart will always start at R4Cx. But the data needs to be plotted to RnCx (where n is the row the data set ends). Specifying what "n" is so that VBA understands the instruction is where I am having difficulty. A sample of code for one of my data series follows.

[vba]ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Output!R4C3:R893C3"
ActiveChart.SeriesCollection(1).Values = "=Output!R4C5:R893C5"
ActiveChart.SeriesCollection(1).Name = "=""Gas Production"""[/vba]

If I write the code so that it looks like "=Output!R4C3:RnC3" a Run-time error '1004' is returned stating that VBA is unable to set the XYValues property of teh series class. Any insight is appreciated.

[Dav1000]

I should add that since this is my first foray into writing data to a chart from VBA I initially recorded a macro of teh charting process and then modified it to suit my needs.