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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

or this

Please Login or Register  to view this content.

I just use this

Please Login or Register  to view this content.

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.