Series.Values vs Chart.SetSourceData

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hans - 19 Jan 2006 16:56 GMT

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

Reply to this Message

Andy Pope - 19 Jan 2006 22:36 GMT

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

> Hi all.
>>From a sheet with sourcedata I generate graphs for each row. Right now
[quoted text clipped - 19 lines]
> Thanx in advance.
> Hans

Signature

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Reply to this Message

Hans - 20 Jan 2006 11:19 GMT

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

> Hi,
>
[quoted text clipped - 49 lines]
> > Thanx in advance.
> > Hans

Reply to this Message