Series.Values vs Chart.SetSourceData

[Hans]

Hi all.

From a sheet with sourcedata I generate graphs for each row. Right now

I am changing a simple graph to a two-series graph where for each
row-iteration I have to change the series, for example to:.
I need two series that take data from the same row:
Series A: (E3;H3;K3;N3;Q3;T3;W3;Z3;AC3;AF3;AI3;AL3)
Series B: (C3;F3;I3;L3;O3;R3;U3;X3;AA3;AD3;AG3;AJ3)

When setting the ranges manually all works well. However I get an error
when assigning the range to the series:
series.values = "E3;H3;K3;N3;Q3;T3;W3;Z3;AC3;AF3;AI3;AL3"

In the simple graph I assigned that same string to
chart.SetSourceData() which worked fine.

It looks as if I am unable to specify separate cells in that range, as
a range of "A1:A10" works well.

Does anyone have a clue how to get these separate cells assigned to the
series?

Thanx in advance.
Hans

 

[Andy Pope]

Hi,

You can try this.

Sub NoAdjacentSeries()
'
' Note: This will fail if the Series formula is too long
'
Dim rngTemp As Range
Dim rngArea As Range
Dim strData As String
Dim strJoin As String

Set rngTemp = Range("E3,H3,K3,N3,Q3,T3,W3,Z3,AC3,AF3,AI3,AL3")
strJoin = "=("
For Each rngArea In rngTemp.Areas
strData = strData & strJoin & "'" & _
rngArea.Parent.Name & "'!" & _
rngArea.Address(ReferenceStyle:=xlR1C1)
strJoin = ","
Next
strData = strData & ")"

ActiveChart.SeriesCollection(1).Values = strData

End Sub

Cheers
Andy

 

[Hans]

Thank you Andy.
The resulting string-format in strData was de solution.
Hans