Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default Help! Plotting to scatter graph

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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default Help! Plotting to scatter graph

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" wrote in message
ups.com...
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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default Help! Plotting to scatter graph

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


On Jun 15, 7:24 pm, "Peter T" <peter_t@discussions wrote:
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" wrote in message

ups.com...

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



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,582
Default Help! Plotting to scatter graph


"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 Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default Help! Plotting to scatter graph

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

Cheers,
Kate

On Jun 18, 6:12 am, "Jon Peltier"
wrote:
"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 Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______





  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default Help! Plotting to scatter graph

"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



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,582
Default Help! Plotting to scatter graph

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





  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default Help! Plotting to scatter graph

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

"Jon Peltier" wrote in message
...
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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Scatter PLotting using Data in third colum to determine range maugak Charts and Charting in Excel 2 July 29th 09 03:34 PM
Plotting 3 column data as mutiple xy scatter plot lines on same graph Mahurshi Akilla Excel Discussion (Misc queries) 0 April 10th 08 07:17 PM
Scatter chart not plotting correct range Bruce Charts and Charting in Excel 1 October 7th 06 03:29 AM
Plotting Step Graph with XY Scatter Ralph Heidecke Charts and Charting in Excel 2 December 6th 05 11:30 PM
plotting 2 variables as scatter and naming each point Jane Charts and Charting in Excel 1 June 22nd 05 03:55 PM


All times are GMT +1. The time now is 10:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"