help with named ranges please
 

I am having trouble with using named ranges. I want to use them to create
charts in code.
I have

srt = Cells(1, i).Value & "_sort"
cmf = Cells(1, i).Value & "_cumf"
ActiveWorkbook.Names.Add Name:=srt, RefersTo:=Range(Cells(2, 5),
Cells(lastrow, 5))
ActiveWorkbook.Names.Add Name:=cmf, RefersTo:=Range(Cells(2, 7),
Cells(lastrow, 7))

and this set up the named ranges ok. The I want to add a chart:

ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Data").Range("a1"),
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "'Data'!srt"
ActiveChart.SeriesCollection(1).Values = "'Data'!cmf"

This fails at the last line with "Unable to set the Values property...."
Could someone tell me what the syntax is please and what the difference is
between the last 2 lines, why one works and one doesn't?

Thanks
Chris

help with named ranges please
 

this looks wrong
ActiveChart.SetSourceData Source:=Sheets("Data").Range("a1"),
should be the range of data, so if A1:KK200 is named DataTable
ActiveChart.SetSourceData Source:=Sheets("Data").Range(cmf),

This line
ActiveChart.SeriesCollection(1).XValues = "'Data'!srt"
You don't have a range called "srt"
try
ActiveChart.SeriesCollection(1).XValues =Range(srt)
the last line isn't necessary if you already set the source


Your confusion is that you set variables to the range, then try to hard code
the name

text = "abc"

Range("text") is incorrect
Range("abc") is correct
Range(text) is correct





"inquirer" wrote:

I am having trouble with using named ranges. I want to use them to create
charts in code.
I have

srt = Cells(1, i).Value & "_sort"
cmf = Cells(1, i).Value & "_cumf"
ActiveWorkbook.Names.Add Name:=srt, RefersTo:=Range(Cells(2, 5),
Cells(lastrow, 5))
ActiveWorkbook.Names.Add Name:=cmf, RefersTo:=Range(Cells(2, 7),
Cells(lastrow, 7))

and this set up the named ranges ok. The I want to add a chart:

ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Data").Range("a1"),
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "'Data'!srt"
ActiveChart.SeriesCollection(1).Values = "'Data'!cmf"

This fails at the last line with "Unable to set the Values property...."
Could someone tell me what the syntax is please and what the difference is
between the last 2 lines, why one works and one doesn't?

Thanks
Chris

help with named ranges please
 

not sure if my reply got there. here's a shorter version anyway

you are not using the variable correctly

To use a range named "ABC"

text = "abc" or text = Range("C1") & "_abc" is good

Range("abc") is good
range(text) is good
Range("text") is bad..there's no range named "text", text is the variable

so
ActiveChart.SeriesCollection(1).XValues = "'Data'!srt"
is BAD
ActiveChart.SeriesCollection(1).XValues = Range(srt)

you don't need the last line you set the data source correctly
ActiveChart.SetSourceData Source:=Sheets("Data").Range("a1"),
ActiveChart.SetSourceData Source:=Sheets("Data").Range("datatable")
or
text = "datatable"
ActiveChart.SetSourceData Source:=Sheets("Data").Range(text),




"inquirer" wrote:

I am having trouble with using named ranges. I want to use them to create
charts in code.
I have

srt = Cells(1, i).Value & "_sort"
cmf = Cells(1, i).Value & "_cumf"
ActiveWorkbook.Names.Add Name:=srt, RefersTo:=Range(Cells(2, 5),
Cells(lastrow, 5))
ActiveWorkbook.Names.Add Name:=cmf, RefersTo:=Range(Cells(2, 7),
Cells(lastrow, 7))

and this set up the named ranges ok. The I want to add a chart:

ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Data").Range("a1"),
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "'Data'!srt"
ActiveChart.SeriesCollection(1).Values = "'Data'!cmf"

This fails at the last line with "Unable to set the Values property...."
Could someone tell me what the syntax is please and what the difference is
between the last 2 lines, why one works and one doesn't?

Thanks
Chris

help with named ranges please
 

Thanks for your replies, I think I have it under control now
Chris

"Patrick Molloy" wrote in message
...
not sure if my reply got there. here's a shorter version anyway

you are not using the variable correctly

To use a range named "ABC"

text = "abc" or text = Range("C1") & "_abc" is good

Range("abc") is good
range(text) is good
Range("text") is bad..there's no range named "text", text is the variable

so
ActiveChart.SeriesCollection(1).XValues = "'Data'!srt"
is BAD
ActiveChart.SeriesCollection(1).XValues = Range(srt)

you don't need the last line you set the data source correctly
ActiveChart.SetSourceData Source:=Sheets("Data").Range("a1"),
ActiveChart.SetSourceData Source:=Sheets("Data").Range("datatable")
or
text = "datatable"
ActiveChart.SetSourceData Source:=Sheets("Data").Range(text),




"inquirer" wrote:

I am having trouble with using named ranges. I want to use them to create
charts in code.
I have

srt = Cells(1, i).Value & "_sort"
cmf = Cells(1, i).Value & "_cumf"
ActiveWorkbook.Names.Add Name:=srt, RefersTo:=Range(Cells(2, 5),
Cells(lastrow, 5))
ActiveWorkbook.Names.Add Name:=cmf, RefersTo:=Range(Cells(2, 7),
Cells(lastrow, 7))

and this set up the named ranges ok. The I want to add a chart:

ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Data").Range("a1"),
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "'Data'!srt"
ActiveChart.SeriesCollection(1).Values = "'Data'!cmf"

This fails at the last line with "Unable to set the Values property...."
Could someone tell me what the syntax is please and what the difference
is
between the last 2 lines, why one works and one doesn't?

Thanks
Chris