Using VBA to name ranges and create charts with those ranges

Vote count:

0

I'm new to VBA. I'm attempting to create many clustered column (vertical bar) charts using two columns of information. The first column is a set of names I'd like to use to create named ranges (ie: Line1, Line2, etc.) and the second is the indirect reference of the range without the INDIRECT function (ie, "Sheet1!C4:D28"). I noticed that if the chart references that range, it pulls it up nicely (but I have to include the INDIRECT formula in the reference first, eg: "=INDIRECT(Sheet1!C4:C28)"). The ranges won't change so they don't need to be dynamic. In reviewing prior questions I couldn't seem to find a solution that grabs the first cell in the set and names it, then uses the second cell to define that range. I think I might need the ActiveWorkbook.Names.Add Name:= formula (but I couldn't get it to use a selection or cell to define the name) and combine it with a loop. If named ranges need a "hard coded" name and can't be dynamically named, I can go back and extract the array (C4:D28) from the cell and have the chart reference that if it makes the code simpler. I'm guessing my first outlined attempt isn't the only solution and there's probably one much more elegant.

Populating a new sheet with all the charts for each of these ranges would be icing on the cake, but I'll be happy enough receiving help to get the data set up to chart.

Any suggestions would be appreciated. Thank you!

asked 1 min ago

user3582338

1