"Unable to set the Formula property of the Series class" with a tw

I have built large workbook of software test metrics for dozen of products
(by rows) with results organized by week over the columns. I have created
dynamic ranges that select a moving window of dates (for the x-axis) and a
corresponding moving window of metrics for each metric and product.

I have created a row of reference charts for the first product. My VBA code
copies the reference charts for each prduct under test and pastes them in a
grid, then rewrites the series formulas of the charts to reference the
correct products.

The formula re-writing code is failing with the dreaded "Unable to set the
Formula property of the Series class" error. The original formula and the
replacement formula are each 144 characters in length, though several of the
terms in the formula are dynamic ranges. I haven't seen a definitive
statement on whether dynamic ranges are expanded before the 255 char VBA
formula limit is applied.

Here's the original formula for one of the series in a collection of 3 for
one chart:
=SERIES(Weekly_Release_Data!$E$27,'Sample-Defects-Test-Data.xls'!DateTextSpan,Weekly_Release_Data!'RelW00 1_Total_Test_Pass_Rate_Span',1)

(The quotes around "RelW002_Total_Test_Pass_Rate_Span" were returned by the
..formula property - they do not show in the formula bar.)

I want to change the "001" to another value, and have used various
approaches, each of which results in the error.

RelW001_Total_Test_Pass_Rate_Span is defined as:
=OFFSET(INDEX(RelW001_Total_Test_Exposure_Rate,1,C urCol-3),0,-MIN(PeriodsPast,CurCol-5)-1,1,MIN(PeriodsPast,CurCol-5)+PeriodsForward)

RelW001_Total_Test_Exposure_Rate is defined as:
=Weekly_Release_Data!$F$25:$BK$25

PeriodsPast and PeriodsForward are defined as small constants like 26 and 13.
CurCol is defined as Control!$C$3.

All the named ranges are generated by VBA code from row titles, so it is
possible to shorten them, but I would need a lookup table to convert from the
"plain english" titles to condensed names if that is required to get under
some limit.

Thanks in advance..
-- Peter

Many hours later I have found a solution to this problem. Excel Developers,
please note the following:

1. If you record a macro of the steps involved in editing a SERIES formula
in the formula bar, the macro recorder generates code which accesses the
..formula property of the SeriesCollection. This is wrong. If you originally
assigned a cell to the series name, the .formula property will return an
absolute reference for the first argument of the SERIES formula. You must use
the .formulaR1C1 property to retrieve the formula.

2. The macro recorder is incorrectly placing single quotes around a range
name used for the Y values. You must remove the quotes from the formula
before assigning it to the .formula property. (Note that I did not assign it
to the .formulaR1C1 property, but it does work.)

My new code looks like this:
For i = 1 To cht.SeriesCollection.Count
formula = ActiveChart.SeriesCollection(i).FormulaR1C1
Debug.Print "Original formula"
Debug.Print formula
formula2 = WorksheetFunction.Substitute(formula, "RelW001", "RelW" &
Format(chartproduct, "000"))
' The formula returned by Excel has a bug in it - there are extra
single quotes around the range
formula2 = WorksheetFunction.Substitute(formula2, "!'", "!")
formula2 = WorksheetFunction.Substitute(formula2, "',", ",")
Debug.Print "New formula"
Debug.Print formula2
ActiveChart.SeriesCollection(i).formula = formula2
Next i


"PeterQ" wrote:

I have built large workbook of software test metrics for dozen of products
(by rows) with results organized by week over the columns. I have created
dynamic ranges that select a moving window of dates (for the x-axis) and a
corresponding moving window of metrics for each metric and product.

I have created a row of reference charts for the first product. My VBA code
copies the reference charts for each prduct under test and pastes them in a
grid, then rewrites the series formulas of the charts to reference the
correct products.

The formula re-writing code is failing with the dreaded "Unable to set the
Formula property of the Series class" error. The original formula and the
replacement formula are each 144 characters in length, though several of the
terms in the formula are dynamic ranges. I haven't seen a definitive
statement on whether dynamic ranges are expanded before the 255 char VBA
formula limit is applied.

Here's the original formula for one of the series in a collection of 3 for
one chart:
=SERIES(Weekly_Release_Data!$E$27,'Sample-Defects-Test-Data.xls'!DateTextSpan,Weekly_Release_Data!'RelW00 1_Total_Test_Pass_Rate_Span',1)

(The quotes around "RelW002_Total_Test_Pass_Rate_Span" were returned by the
.formula property - they do not show in the formula bar.)

I want to change the "001" to another value, and have used various
approaches, each of which results in the error.

RelW001_Total_Test_Pass_Rate_Span is defined as:
=OFFSET(INDEX(RelW001_Total_Test_Exposure_Rate,1,C urCol-3),0,-MIN(PeriodsPast,CurCol-5)-1,1,MIN(PeriodsPast,CurCol-5)+PeriodsForward)

RelW001_Total_Test_Exposure_Rate is defined as:
=Weekly_Release_Data!$F$25:$BK$25

PeriodsPast and PeriodsForward are defined as small constants like 26 and 13.
CurCol is defined as Control!$C$3.

All the named ranges are generated by VBA code from row titles, so it is
possible to shorten them, but I would need a lookup table to convert from the
"plain english" titles to condensed names if that is required to get under
some limit.

Thanks in advance..
-- Peter