Need help using columns series or sequence

**tyler3** · 12-06-2006, 02:17 PM

I'm trying to write a macro that will update charts.

Basically in the example picture linked below, columns A-C are plotted in the two charts. (The real workbook has a lot more data.) I want to develop a macro that will update to charting columns B-D, then C-E, etc; shift the range by 1 column to the right every time I run it. I hope to alleviate the need to manually click on each chart and select the new source data. This seems like it would be pretty simple but I can't figure out the syntax in vba. I don't have a lot of experience coding in vba; I usually use the macro recorder and then edit parts of the code as necessary.

Thanks for any help, sorry if this has been addressed before or covered recently.

http://i67.photobucket.com/albums/h294/t3355/excel.jpg

**Mallycat** · 12-06-2006, 03:21 PM

As fate would have it, I was reading a book last night that explains how to do exactly this.

Have a look at my attached example. The key is to first create 2 named formulas. Have a look at them in this example. To create ChartTitle, I selected cell A3, then added the name with a relative reference =offset($A3,0,0). I then created the name ChartData =offset($A3,0,1,1,3) again with cell A3 selected first. I then created a chart as I wanted to see it (with 1 series). Select the series and change the fixed ranges for chart title and chart data to these named ranges.

In your example, you would need to flip this go be columnwise rather than row wise.

Matt

(John Walkenbach's excellent Excel Formulas book if you are interested)

**tyler3** · 12-06-2006, 04:36 PM

Thanks,

I like how easy that is to update but is it possible to plot several sets of data at the same time like this? For instance, when I select the Matt and Peter rows (Matt first), press F9, it only graphs Matt. It only graphs the first row selected.

The original way I was trying to accomplish this was

Dim x as Object
Dim y as Object

x = (A1:C10)
y = (A16:C25)

I get an error with the colon, semi-colon and comma. I thought an object could be a range?

then somehow setup a statement allowing the macro to shift the range one column to the right each time it is run. The problem is I don't know how to "add" a column or shift it. Is that possible? Something like (I know the syntax is wrong): (A1:C10) + ____ = (B1:D10), then (B1:D10) + ____ = (C1:E10).

I guess I'm too much of a vba n00b right now. I've been trying to google search for something similar but I can't find anything. I might consult the library.