Create Chart with multiple collections definde by cells

Question

Create Chart with multiple collections definde by cells

tuli (Electrical)

(OP)

15 Nov 17 14:51

Hello,
I am trying to create a chart with many plots and would like to do something similar to the code below.

Sub Macro1()
'
Dim R1, R2, R3, R4, R5, R6, R7 As Range
Dim RR(11) As Range
Sheet2.Select
For i = 0 To 11
Set RR(i) = Range(Cells(9, 2 + 9 * i), Cells(209, 2 + 9 * i))
Next i
'
Range(RR(0),RR(1),RR(2),RR(3),RR(4),RR(5),RR(6),RR(7),RR(8),RR(9),RR(10),RR(11)).Select

ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range(RR(0),RR(1),RR(2))
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = CELLS(8,2)
ActiveChart.FullSeriesCollection(1).XValues = RR(0)
ActiveChart.FullSeriesCollection(1).Values =RR(1)
FOR I=2 TO 11
ActiveChart.FullSeriesCollection(I).Name = CELLS(8,2 + 9 * i)
ActiveChart.FullSeriesCollection(I).XValues = RR(0)
ActiveChart.FullSeriesCollection(I).Values =RR(I)
NEXT I
End Sub

Please advise

Thank you

SkipVought (Aerospace) · Answer 1 · 2017-11-15 15:22:53

Hi,

What problems are you experiencing with your code and/or with your chart result?

Plz upload representative sample source data with 12 columns of data.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

tuli (Electrical) · Answer 2 · 2017-11-15 16:25:25

well, for starters, it just fails on

Range(RR(0),RR(1),RR(2),RR(3),RR(4),RR(5),RR(6),RR(7),RR(8),RR(9),RR(10),RR(11)).Select

what I am trying to do is defining an ARRAY of ranges, so I can use the definitions of the series collections in a loop (using the cells method)

SkipVought (Aerospace) · Answer 3 · 2017-11-15 16:34:49

Try Union instead of Range.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

tuli (Electrical) · Answer 4 · 2017-11-15 17:16:25

Thanks, I passed that line.

Now it fails on

ActiveChart.SetSourceData Source:=Range(RR(0), RR(1), RR(2))

I do not know how to proceed with the "Union"

Thanks

SkipVought (Aerospace) · Answer 5 · 2017-11-15 20:11:07

Quote:
I do not know how to proceed with the "Union"

What does that mean?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

SkipVought (Aerospace) · Answer 6 · 2017-11-16 00:39:27

This code loads the chart with the data you selected.

CODE

'
    Union(RR(0), RR(1), RR(2), RR(3), RR(4), RR(5), RR(6), RR(7), RR(8), RR(9), RR(10), RR(11)).Select

    ActiveSheet.Shapes.AddChart 240, xlXYScatterLinesNoMarkers

Is that what you intended?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Come Join Us!

Posting Guidelines

Related Projects

Jobs

Create Chart with multiple collections definde by cells