Hi,
I'm trying to extend the chart according to new added values. Here is the sample code i have recorded through a macro, but it doesn't seem to work when i run the code? Any help or tweak to the code is much appreciated. Thanks in advance!
The same has been posted here:
Please Login or Register to view this content.
http://www.vbaexpress.com/forum/show...176#post391176
Last edited by JayeshG; 05-31-2019 at 02:17 PM.
It's a bit more difficult when you have the data going across instead of down. It is easy to get a row number or a column number. However with a column number you have to convert it to a column letter. Also, if you had the data going down, then you would not need VBA at all, put the data into an Excel Table and building the chart off of it.
However, there is a third option and that does not involve Tables or VBA: named dynamic ranges. They will work no matter how your data is organized.
Here are two wikis: one describes how to make a named dynamic range and the other how to use them in charts.
http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
http://www.utteraccess.com/wiki/Dynamic_Charting
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Thanks, dflak. My question is if we can change the chart range manually even though data is spread across, then we can have some solution of this through VBA. I tried different ways through code to change the chart but still unsuccessful.
Does "extend the range" mean columns beyond Z, or series based on cell A2?
Why not post the sample file?
Ben Van Johnson
Sure protonLeah. Thanks! Here is the sample file attached...
FYI - I've modified the data so ignore the range values in code above. Thanks!
Currently, the chart shows the values from year 2008 through 2017, i need a vba which can extend the chart till year 2028 with rest of the data points or series. Let me know if you need more info. Thanks!
Last edited by JayeshG; 05-31-2019 at 07:03 PM.
Option One: no vba dynamic name ranges:
Adj.Cash =OFFSET(Schart!$D$25,0,0,1,NumYears)
Adj.Rtn =OFFSET(Schart!$D$23,0,0,1,NumYears)
Eco.Profit =OFFSET(Schart!$D$24,0,0,1,NumYears)
Goodwill =OFFSET(Schart!$D$26,0,0,1,NumYears)
NumYears =COUNT(Schart!$21:$21)
REV =OFFSET(Schart!$D$22,0,0,1,NumYears)
SeriesNames =Schart!$A$22:$A$26
x.axis =OFFSET(Schart!$D$21,0,0,1,NumYears)
Got this resolved here:
http://www.vbaexpress.com/forum/show...176#post391176
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks