Re: Help! Plotting to scatter graph

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
***).

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 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)

Regards,
Peter T

"katem" <kate.maun...@xxxxxxxxx> wrote in message

news:1181887520.288179.16040@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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


.

Quantcast