Changing Chart Objects in Code

[Geoff Goodacre]

Can anyone please explain why the first statement marked
OK works but the second doesn't? I've checked that the
sheet names are consistent with the sheets in the actual
workbook. Any offers welcome!

Windows(CurrentWorkbook).Activate
Sheets("Comparative graph").Select
ActiveSheet.Unprotect
If CompGraph = "L" Then
ActiveSheet.ChartObjects("Chart 3").Activate '
2nd Comparative Graph
Else
ActiveSheet.ChartObjects("Chart 4").Activate '
3rd Comparative Graph
End If
(OK)ActiveChart.SeriesCollection(1).Values = _
"='" & CFLsheetName & "'!R6C40:R57C40"
(Unable to set the value)ActiveChart.SeriesCollection
(2).Values = _
"='" & JSsheetName & "'!R6C50:R57C50"
ActiveSheet.Protect

 

[Geoff Goodacre]

I noticed that the cells referred to in the first series
contained values, and those in the second series were
empty (the code is building a template that will be
populated with numbers in a later process). I keyed
zeroes into the cells referenced and the code now works.
The same action carried out manually does not object to
the empty cells.

This 'quirk' of at least Excel 97 and 2000 doesn't seem to
be documented anywhere!