Series.Values vs Chart.SetSourceData

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

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 wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info

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


Andy Pope wrote:
> 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 wrote:
> > 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, Microsoft MVP - Excel
> http://www.andypope.info