Chart data range update with VBA - Excel 2010

[cwunderlich]

I have a sheet with many different line charts. Each line chart uses source data from different sheets within the file. By default, I have the macro create all these source data / charts using all years of data. However, I want to build something which allows the user to manually enter a number of years they would like displayed on every chart. I should mention that the last cell used in the source data reference will always be the last cell (ie the number entered by the user will dictate how many years to go back from here)

For the design I was thinking something along these line:

- Loop through each chart in the sheet
- for each chart, find out what data range is being used as the source
- change the data range to start at the original last year and only go back the user-specified number of years

I am not very familiar at all with syntax to manipulate graps/charts. Does anyone know the syntax for this sort of thing?

Thank you very much.

 

[Andrew Poulsom]

Couldn't you use dynamic ranges?

 

[cwunderlich]

do you mean have the chart series refer to a named range and then change the named ranges on each sheet?

 

[Andrew Poulsom]

Yes, there are some examples here:

http://peltiertech.com/Excel/Charts/Dynamics.html#DynColCht

You can use the cell that contains the number of years in the OFFSET formula.

 

[cwunderlich]

Yes, there are some examples here:

http://peltiertech.com/Excel/Charts/Dynamics.html#DynColCht

You can use the cell that contains the number of years in the OFFSET formula.

while I am not sure if that link helped me at all, your post helped me recall a named range I used in a past file which utilized the OFFSET function. I believe this should do the trick without any vba coding necessary!

Thanks!

-Chris