Dear Friends:
I have several files in a folder. All files have only one worksheet and already have one chart. I wish to add a second line chart with a partial range, that is last 20 rows of column C & column G. Please note that number of rows vary from file to file, i.e, one file may have 400 lines, the other may have 365 and so on! The chart should cover only the last 20 rows
Hi,
See attached file. I hope this what you need.
This is done without VBA. I just used 4 names (see name manager). See chart 'Data Source', where this names are used.
Elio Fernandes
Not exactly what you asked for but take a look at the attached and see if it works for you. Good Luck!
Tom S.↙ If you find my reply helpful click on the * down there on the left. Yeah that's it, right there, down on the leftIf your question is resolved, mark it SOLVED using the thread tools.
I found your data interesting and tested some chart features on it and found an interesting combination of features. Not sure if you can see them all if you are still on Excel 2003 but take a look and see what you think.
Hi efernandes67:
Your chart seems ok but I want a VBA as I wish to plot the chart in 1000's of files in different folders. Secondly, I wish to retain my original chart and add this other chart just below the first chart and I want no other details in the file, just add a second chart. The macro should pick up the last 20 rows from each file. Please note these last 20 rows (row numbers) will differ from file to file because total rows in each file is different.
Hi Tank997:
Your chart also seems ok but as I have said above, add no other details in the file, just add the second chart immediately below the first chart. I could not see your macro because the file was read only.
The only solution to my problem is through a macro and not a manual charting.
GDM69,
I misread you original post. Sounds like you want one macro to open each file in the folder and add a second chart to them. That's a little over my head.
The second file is an Excel 2007 file so that may be why you are getting the read only error. The VBA code in the files is only two lines to set the min and max for the slide bar. It works off one cell on the sheet so you can change the graph to scroll through the last 20 rows or all the rows in the file,20 at a time, from bottom to top. I've been playing with the side bar on a charts feature and your data was a good way to test some things. I thought you might find it interesting and useful as well. Good Luck!
Hi,
See module in attached file.
1) Run macro: DefineNames
2) Run macro: CreateChart
If you change the layout of Sheet1 you may have to change the DefineNames macro.
Elio Fernandes
Hi Elio:
Thanks for the file with 2 modules. I tried running them but get an error message
Compile Error:
Method or data member not found
The highlight points to this line:
ActiveChart.SetElement (msoElementLegendNone).
I am not going to change any layout of sheet1. Please look into and advise. Another thing: Can we remove this line
Points to Plot 20 by input sort of this?
By the way, is the above error is because of imcompability of Excel 2003 and 2007?
Last edited by GDM69; 03-20-2013 at 02:48 AM.
have you made any effort yourself-such as recording a macro while creating one chart the way you want it to look? many posters will simply ignore requests where the asker has made no obvious effort of their own so it is in your own interests ;-)
Josie
if at first you don't succeed try doing it the way your wife told you to
I have of course made efforts to this effect. I can actually create a chart with full range or fixed range but the tricky part is how to describe a range such as "LAST 20 rows". First 20, 50, 80 rows is no problem because those will be permanantly defined rows but last 20 rows is a different thing as the row numbers will get changing with file to file as every file is of different number of rows.
if you record a macro while setting up the chart you want-using a fixed range-and then provide the code it will be a lot easier for someone to adjust that code than to create it from scratch as well as demonstrating that you are not simply expecting someone to do all the work for you. I note you have also posted a very similar question in the charting area-please do not duplicate posts.
Here is the code line that I use to create a full data range chart.
.SetSourceData Source:=wksSource.Range("C2:C" & LastRow & ",G2:G" & LastRow), PlotBy:=xlColumns
I wish to make appropriate changes at red colored places so that the code line will take last 20 values from column C and G. I am not very conversant with VBA and this kind of tweaking is beyond my head. Any solutions?
Here is the code line that I use to create a full data range chart.
.SetSourceData Source:=wksSource.Range("C2:C" & LastRow & ",G2:G" & LastRow), PlotBy:=xlColumns
I wish to make appropriate changes at red colored places so that the code line will take last 20 values from column C and G. I am not very conversant with VBA and this kind of tweaking is beyond my head. Any solutions?
Last row is defined as follows: LastRow = .Cells(.Rows.count, "C").End(xlUp).Row
Last edited by GDM69; 03-20-2013 at 10:15 AM.
I have saved the file to my desktop and I its working!!!
Try it again.
Hi:
I tried the file (code) once again but no luck! Same problem. I did F8 also (with CreateChart) but the cursor goes directly to the last code line (bypassing all other lines) and displays the same error as reported earlier. In case of DefineNames the cursor does go in a proper sequence without any error message, and as I have said earlier, I am not going to make any changes with Sheet1.
Any clues?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks