Help! Plotting to scatter graph

[katem]

Hi,

Was wondering if someone could give me a hand - have been fiddling a
while with no success. Am trying to plot a range from a huge number
of worksheets onto a scatter graph - have been able to create the
graph etc, but having no luck assigning the x & y values.

The code I've tried (which is in a loop) is:-

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

The name part works, but the x & y values don't. I keep getting
graphs with the right number of lines, but the data being plotted is
1,1 in all cases.

Thanks in advance for your help!
Kate

 

[Peter T]

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)

Regards,
Peter T

 

[katem]

Peter,

Thanks for your help - I understand that an array of values fed to a
graph has to be short that 255 characters (run up against this one
before!), but are you saying that this will happen with your amended
code? What's the best way to read in a 2 columns of numbers to send
them to be graphed (asumming they may not be a constant length on each
sheet).

Thanks so much for your help,
Kate

 

[Jon Peltier]

Hi Kate,

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


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

 

[katem]

Thanks guys, that worked a treat - you just saved me hours of
repetition!!

Cheers,
Kate

 

[Peter T]

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

 

[Jon Peltier]

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

 

[Peter T]

Jon,

Not sure what I was thinking of, if indeed I was thinking! (perhaps
discontiguous ranges for individual series values)

Kate - go with Jon's simpler method if you want to 'source'

Regards,
Peter T