Re: Help! Plotting to scatter graph

Tech-Archive recommends: Speed Up your PC by fixing your registry

"Jon Peltier" <jonxlmvpNO@xxxxxxxxxxxxxxxxxxx> wrote in message
news:#afKkvRsHHA.508@xxxxxxxxxxxxxxxxxxxxxxx

"Peter T" <peter_t@discussions> wrote in message
news:%23e22F8yrHHA.4100@xxxxxxxxxxxxxxxxxxxxxxx
Hi Kate,

You need to qualify 'Cells' to the relevant work***, 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 ***. 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 = Active***.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 = Active***.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



.

Quantcast