Cant make Excel Chart to use Named Range for Series values in VBA

**rocksan** · 04-25-2012, 11:01 PM

Hi guys,

I have simple but unsolved problem trying to make an excel chart refer to named range to pull out the values in VBA. AS you can see in the code I put MyNamed Range instead of $X$X to make it dynamic. But when I run it, it generates a run time error 1004.

ActiveSheet.Shapes.addchart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("MyNamedRange")
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!MyNamed_Range2"

For the record MyNamedRange = Profit_generated_sheet1

Whats wrong?

**rocksan** · 04-26-2012, 03:55 AM

Seems I cant use the method above. I want to try another method, this one involves using the match application. But I still need help to complete this code.

Dim rng as range
Dim X as string 

rng= activesheet.range (1:1)
X = Application.Match(MyNamedRange, rng, 0)

Basically, the code above finds MyNamedRange in the first row, which refers to the column headers for a match.

I want the code to also select the whole column with values after matching with the header.

The X will be the reference when I have to determine the column with data for my chart in VBA macro.

Can anyone please help complete the code?

To summarize, I need to:
1. Match X with MyNamedRange in Rows 1:1
2. Select all value in MyNamedRange column and keep in memory, assigned to X.
3. X will be used for reference for chart series data.

**rocksan** · 04-26-2012, 04:10 AM

Attached is the template of my data.

What I want to do is to create a chart from it, where the ranges change in column position frequently, so my macro must be able to adapt to the changing column positions, based on the header string not the column number.

ChartMacro.xlsm

ActiveSheet.Shapes.addchart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Dog"""
    ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$C$2:$C$21"
    ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$A$2:$A$21"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "=""Cat"""
    ActiveChart.SeriesCollection(2).Values = "='Sheet1'!$B$2:$B$21"
    ActiveChart.SeriesCollection(2).XValues = "='Sheet1'!$A$2:$A$21"

The data pasted is only a fraction of it.

**JosephP** · 04-26-2012, 06:15 AM

your named ranges were wrong-if you look at them in Name Manager you can see that they are text, not ranges:
="'Sheet1'!$B$2:$B$21"
instead of
='Sheet1'!$B$2:$B$21

so you need to change the name creating code to use

            RefStr = "='" & Wks.Name & "'!" & Rng.Address

instead of

            RefStr = "'" & Wks.Name & "'!" & Rng.Address

and then you can use them in the charts.

**rocksan** · 04-26-2012, 07:10 AM

I've found out how to do it, finally. The solution is so simple makes me look stupid. Instead of using:-

This

ActiveChart.SeriesCollection(1).XValues = "=Row_labels_sheet1"

or this

ActiveChart.SeriesCollection(1).XValues = Range ("MyNamedRange")

I just use this

ActiveChart.SeriesCollection(2).XValues = [Row_labels_sheet1],

and now the chart can find it. The difference is just using these "[]" instead of this "()"

I Can't believe it took me almost a day to figure it out.

I'll close this thread tomorrow, see if has another way of doing it.