Modification Range Of Charts

Originally Posted by my_aks

Hi Guys,
I have to handle a task of report preparation in excel, a part of which enatils me to log the values of vital variables every week and update the graph to show the data for last x weeks. for example, if the range for x axis is c:4 to c:10 the next week will have range from c:5 to c:11.
i have to make similar modifications for around dozen graphs. this task is quite mechanical and i was wondering if a vba scipt could be written to do the same work.
i am new to excel and vba. i have strated reading "Writing Excel Macros with VBA 2nd Edition". But, meanwhile if any of you guys could tell me whether it is possible and if poss. , how it would be a big help.

Yes, Its possible in excel. However, there is no standard script as such. The script depends on the requirements. Can you send me the sample files and little more details so that I can prepare a macro for you?

Originally Posted by jbrumels

I have a very similar problem. I have a chart setup up that shows data in column B through M in a particular row. Once a month I delete column B and I would like the chart to automatically update to show the new data (former columns C thorough N). Sample file is attached

Hi,

I am not sure if that will work for you or not. I have added the button on the top of your chart. If you click that button, the range would be set automatically. Let me know if it doesn't work for you.

Originally Posted by my_aks

There are many type of charts on which I have to work but most of them can be divided into either the Type A or Type B or a combination of both.

Type A Graphs : Here I have to increment the lower limit and the upper limit of the X axis and the Y-axis(s) range by 1 (The range( = upper limit – lower limit) remaining the same) every week.
For eg. The range for X axis for ‘Search by FNN’ is ='TABLE A-2'!$G$119:$G$147. For next week I will have to modify it to 'TABLE A-2'!$G$120:$G$148 and so on for the next week.
Similarly, Average Values will have to be changed from ='TABLE A1- CHART A'!$AE$156:$AE$184 to ='TABLE A1- CHART A'!$AE$157:$AE$185. Same with Average values.

Type B graphs: Here the no. of rows of might change from week to week. So, I have to modify the range in the chart so as to include all the rows except the 'total' row. ( The lower limit remains constant , the upper limit varies )
Here, I think if you can check whether a cell is empty it would be easier to code for this.
A rough logic flow would be like this :
Check cell empty for G(4+i) (i=0; i++)
If cell is empty THEN range is from G4 – G(4+i-2)


P.N. - The no. of columns remains constant for all the charts.

Hi,

I have prepared a document with your data. When you goto the first sheet and click on Run Macro button, it asks you that which sheet you want to apply your logic for. For example, if you want to increase the series by row in every sheet..then you will select All, and hit increase button. It will increase all of the series in every chart of all the sheets by one row. However, I skipped your B Table section, and unfortunately I have no time till monday to look into it. I would look into ur file once again after weekend.

Hope you wont mind