WHY is the code not working?

music4fun

New Member
Joined
Jul 20, 2007
Messages
11
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
:oops:
run-time error '9':
Subscript out of range
:unsure: Has somebody an idea?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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:

Code:
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

New Member
Joined
Jul 20, 2007
Messages
11
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Jul 20, 2007
Messages
11

ADVERTISEMENT

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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

New Member
Joined
Jul 20, 2007
Messages
11
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,441
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top