WHY is the code not working?

[music4fun]

variable for a series in a graph.

variable = Sheets("pick your range").Range("C16").Value
' cell C16 has the range as a string
Workbooks("my workbook").Sheets("graphs").Charts("Chart1").SeriesCollection(1).Values = variable

=> Runtime error 438:
Object doesn't support this property or method

What am I doing wrong? Please help

UPDATE:
I guess object SeriesCollection does not support value method.
OK. So I check what methods are available.
Solution in Excel VBA help:

Worksheets("pick range").Range("E20").Copy

'E20 contains the string for the range

Charts("Chart6").SeriesCollection.Paste

run-time error '9':
Subscript out of range
Has somebody an idea?

 

[lozzablake]

you have to assign either a range object or an array to the Values object. You cannot assign a string. Also if the chart is embedded in the worksheet (not a chart sheet) you have to use the ChartObjects object. So:

Sub chkChart()

    Dim myString As String
    Dim myRange As Range
    Dim myChart As Chart
    
    
    myString = Range("e20")
    Set myRange = Range(myString)
    Sheets("sheet1").ChartObjects(1).Activate
    Set myChart = ActiveChart
    
    myChart.SeriesCollection(1).Values = myRange


End Sub

 

[music4fun]

right track

Thanks so much for taking on my problem

I copied the code but when it hits the line
Set myrange = Range(mystring)
I am getting the error
Run-time error '1004': Method 'Range" of object '_Worksheet' failed

Hope you can solve that one, thanks!

 

[Richard Schollar]

What exactly does E20 contain? Do you have an embedded chart object (ie in a normal worksheet) or is it a separate chart sheet?

 

[music4fun]

=CONCATENATE("'attend'!$",C6,"$",C3,":$",C8,"$",C3)
which results in the string
'attend'!$J$3:$AA$3

 

[music4fun]

...and it is an embedded chart object, there are 6 charts on that worksheet

 

[Richard Schollar]

It might be worth breaking up the sheet and range address into separate cells ie so E19 has attend and E20 has j3:AA3 and then set the range reference accordingly:

Set myrange = Sheets(mysheet).Range(mystring)

where mysheet is E19 and mystring E20.

 

[music4fun]

Thanks so much for your help! (I worked on it yesterday and today on and off)
The code runs smoothly until the mysheet!.myrange value is assigned to the seriesCollection.

I am getting error 1004: Unable to set the values property of the series class at line
mychart.SeriesCollection(1).Values = myrange
where myrange = sheets(mysheet).range(myrange)
where mysheet is a string just containing the name of the label of the sheet, no ' or !.
If I change it to 'mylabel' or 'mylabel'! the error is subscript out of range.
Can you help me?

 

[Richard Schollar]

Hi

You've got a PM