Peter -

Indeed that correctly references the range. I think I had already
suggested
that to solve Kate's original problem of not qualifying 'Cells' a dot to
the
relevant sheet. What I meant with 'string formula' was to link the source
to
the series, as a possible alternative (if required or useful) to merely
applying values as arrays.

You suggested linking the source to the series, but mentioned that it took a
lot more string manipulation to do so. I assumed you meant building a
command like this:

ActiveChart.SeriesCollection(i).Values = _
"='" & Worksheets(i).Name & "'!" & _
Worksheets(i).Range(Cells(2, 2), Cells(2, 2).End(xlDown)).Address

when in fact

ActiveChart.SeriesCollection(i).Values = _
Worksheets(i).Range(Cells(2, 2), Cells(2, 2).End(xlDown))

does the same thing without having to build the string, keep track of single
quotes and bangs, etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
"Jon Peltier" wrote in message
...

"Peter T" <peter_t@discussions wrote in message
...
Hi Kate,

You need to qualify 'Cells' to the relevant worksheet, eg

ActiveChart.SeriesCollection(i).Values = Worksheets(i).Range(Cells(2,
2), Cells(2, 2).End(xlDown).Value)

with Worksheets(i)
ActiveChart.SeriesCollection(i).Values = .Range(.Cells(2,2), .Cells(2,
2).End(xlDown).Value)
end with

note the dot before each .Cells

If your sets of values produces arrays as strings in the series formula
with
more than 240-255 characters the method would fail

BTW, unless you have multiple axes you only need to apply the XValues
to
the
first series.

FWIW you could add the range source's to your series instead of arrays
of
values (needs a bit of work to convert correctly to a string formula)

Not much work, since you can reference a range instead of building a
string:

With Worksheets(i)
ActiveChart.SeriesCollection(i).Values = _
.Range(.Cells(2,2), .Cells(2, 2).End(xlDown))
End With

- Jon
-------

Jon,

Indeed that correctly references the range. I think I had already
suggested
that to solve Kate's original problem of not qualifying 'Cells' a dot to
the
relevant sheet. What I meant with 'string formula' was to link the source
to
the series, as a possible alternative (if required or useful) to merely
applying values as arrays.

Kate,

Glad you got it working. If interested following attempts to demonstrate
both applying array values (subject 255 limit) and linking series to
source,
with series data on different sheets -

... in a new workbook, run 'Test'

Sub TestData()
Dim i As Long
' some sample data in col-B in 3 sheets
For i = 1 To 3
With Worksheets(i)
For r = 2 To 4
.Cells(r, 2) = i + r - 1
Next
End With
Next
End Sub

Sub Test()
Dim chtObj As ChartObject, cht As Chart, sr As Series
Dim sF As String
Dim i

TestData

''' array values
Set chtObj = ActiveSheet.ChartObjects.Add(200#, 10#, 240#, 160#)
Set cht = chtObj.Chart

For i = 1 To 3
Set sr = cht.SeriesCollection.NewSeries
With Worksheets(i)
sr.Values = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown)).Value
End With
Next

''''' linked to source

cht.HasTitle = True
cht.ChartTitle.Text = "Array values"

Set chtObj = ActiveSheet.ChartObjects.Add(200#, 200#, 240#, 160#)
Set cht = chtObj.Chart

For i = 1 To 3
Set sr = cht.SeriesCollection.NewSeries
With Worksheets(i)
sF = "= '" & .Name & "'!"
sF = sF & _
.Range(.Cells(2, 2), .Cells(2,
2).End(xlDown)).Address(ReferenceStyle:=xlR1C1)
sr.Values = sF
End With
Next

cht.HasTitle = True
cht.ChartTitle.Text = "Linked to source"

End Sub

Regards,
Peter T