using variable names in series data values

[R.VENKATARAMAN]

I have about 50 rows of dynamic data.
the first column gives the names of the scrips and rest of the columns give
the data
The data will be added daily.
I would like to prepare 50 dynamic charts for each of these 50 rows by using
naming (I have studied the webpages of Jon peltier/ tushar Mehta and
using their ideas extensively and succesfully for individual charts)
But now I have to prepare 50 charts.
I have also prepared a programme to give names to dynamic ranges.
for e.g. name="alfalval"(my name for this formula is the same as cell value)
refersto:==OFFSET(Sheet1!$A$3,0,1,1,COUNT(Sheet1!$3:$3))

I used count instead of countA because all my data from offset(a3,0,1) are
all numbers.
I also prepared dummy charts for a few of the scrips
I call for the source data and when I type in the source data-series-values
of indivdual charts as for e.g.

'holding charts.xls'!alafalval (alfaval is the name of the dynamic range
formula)

it works.

But instead of individually giving the values for each of the 50 charts I
would like to preapre a
programme which can give source values using names collection.
my code reads
===============================
Sheets("alfalaval").Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = Worksheets("sheet1").Range("a3")
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=" 'holdings charts.xls'!" &
names(1).name"
==================================

the code runs but no data is plotted. . in the< sourcedata.values> window
the values are still "={0}"
in the immediate window <?names(1).name > gives "alfalaval" correctly
but the last statement of the code fails to recognise this. perhaps some
type mismatch.
where do I do the mistake?
I hope I made myself clear.
suggestions solicited.
thanks and regards.

 

[Kieran]

Does changing
ActiveChart.SeriesCollection(1).Values = "=" 'holdings charts.xls'!" &
names(1).name"


to


ActiveChart.SeriesCollection(1).Values = "=" & "'holdings charts.xls'!
&
names(1).name"

make a difference

 

[R.VENKATARAMAN]

thank you very much. the delay was because I took your guidance and used it
and tried to find out whether it succeeds. i t does succeed. only snag was
the last double quote was giving compile error and when i removed that it is
ok it worked.

But I could not understand the logic of the first ampersand <&>

I had another problem I wanted to give the cell value itself as name. But
cell values had some spaces in between. even trimming will remove only
advance and trailing spaces and trimming cannot be done by vba. I used the
code
replace what:=chr(32),replacement:=chr(0)
it worked but the cell values had some other characters also like <-> or
<*> etc. when the use same trick like
replace chr(45) aor chr(42) by chr(0) it does not seem to work . I had to
individually remove these characters. But I shall again look into this. any
suggstion on this also will be greatly appreciated.

in forming the dynamic range values for the charts I had to use lots of
tricks using doouble quotes and ampersands.

I would like to greatly appreciate for the very crucial help by you. lots
of thanks.