Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating Multiple Graphs in Excel

Posted on 2011-05-04
30
Medium Priority
?
296 Views
Last Modified: 2012-06-27
I have a spread sheet with 50 rows (one for each student) and 7 columns (one for each test).  I want to create a separate graph of this data for each student (and I don't want to do it one at a time) and I want one graph which shows all lines.  Any help?
0
Comment
Question by:Bob Schneider
  • 10
  • 10
  • 9
29 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35692307
On the graph that shows all lines, you will end up with 50 lines ploted on the chart! Do you think that this will anyway clear to read?!?
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35692517
Here's the code to do that. I've posted a sample sheet for your to test it.

jppinto
Option Explicit
Private Sub chartmaker(ByVal rng_chart As Range)
Charts.Add after:=Sheets(Sheets.Count)
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=rng_chart, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "chart1"
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Sub CreateCharts()
Dim rng_names As Range
Dim rng_data As Range
Dim rng_chart As Range
Dim i As Long

With Sheets("Sheet1")
    Set rng_names = Range(.Cells(1, 1), .Cells(.Cells.Rows.Count, 1).End(xlUp))
    For i = 2 To .Cells(1, "IV").End(xlToLeft).Column
        Set rng_data = Range(.Cells(1, i), .Cells(.Cells.Rows.Count, i).End(xlUp))
        Set rng_chart = Union(rng_names, rng_data)
        Call chartmaker(rng_chart)
    Next i

End With

End Sub

Open in new window

CreateMultipleCharts.xlsm
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1000 total points
ID: 35692759
Try this macro
For i = 2 To 8
    sh.Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$1,Sheet1!$A$" & i & ":$H$" & i)
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = sh.Cells(i, 1)
Next i
    sh.Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$50")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = "All combined"
    ActiveChart.PlotBy = xlRows
End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bob Schneider
ID: 35692764
Yes putting 50 lines on one graph would be a mess.  I will actually not do that but I might want to combine three or four graphs so I as just trying to get a process that I could modify that way.

Thanks for the code but that gave me separate sheets for each test with each kid on the horizontal axis.  I want separate graphs for each student with all tests on each graph.  I will try to modify myself but if it is not too much trouble feel free to send it along.

Thanks a ton for all of your help!

0
 

Author Comment

by:Bob Schneider
ID: 35693442
ssaqibh, it looks like part of the sub is missing?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35693487
Yes you are right. But then how did you accept it?
Sub Macro1()
Set sh = ActiveSheet
For i = 2 To 8
    sh.Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$1,Sheet1!$A$" & i & ":$H$" & i)
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = sh.Cells(i, 1)
Next i
    sh.Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$50")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = "All combined"
    ActiveChart.PlotBy = xlRows
End Sub

Open in new window

0
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 1000 total points
ID: 35693506
"I want separate graphs for each student with all tests on each graph."

My code creates a chart for each student with all tests results! Please check the attached image!

It's my opinion that the points are not well assigned because my solution does what you wanted! Do you agree?

jppinto
Capture.JPG
0
 

Author Comment

by:Bob Schneider
ID: 35693846
jppinto, I muat have my rows and columns mixed up because I get just the opposite.  My students are the rows and my test scores are the columns.

ssaqibh, I get an "Object does not support this method." error....
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35693864
That's what you get when you accept a "solution" without checking it out properly and without exploring all the solutions... good luck!
0
 

Author Comment

by:Bob Schneider
ID: 35693872
I guess so.  Thanks for all your help...
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35694837
BobbaBuoy, is your problem solved or do you still need help?
0
 

Author Comment

by:Bob Schneider
ID: 35695469
jppinto did a very nice job of giving me a solution but there was a little mis-understanding that caused some confusion between rows and columns.  His solution, which was a very nice one, gave me one spread sheet for each test with all kids on it.  I wanted one spread sheet for each kid with all tests on it.  I may be able to do some shifting of code to get this to work...haven't had the time to try yet...but I accepted the solution prematurely.

So, no, the problem is not solved.  Thanks for asking!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35695905
When you get the

"Object does not support this method." error....

can you tell me which line gives this error by clicking the "Debug" button?
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35696542
I don't know how your data is layed out but if it's like on the file I've posted, you should get a sheet for each student with the results of all tests. If you data is not on the same layout as the posted file, you just need to Transpose your data, switching columns for rows and rows for columns.
0
 

Author Comment

by:Bob Schneider
ID: 35698585
What I did was transpose the rows and columns and then I got exactly what I wanted ippinto.  Thanks a ton everyone!!!!!
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35699739
If I provided (also) a solution, shouldn't I get also an "Assisted answer" here?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35699778
jppinto, unless mine works you get full points
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35709454
Still waiting for feeback...
0
 

Author Comment

by:Bob Schneider
ID: 35710546
I assume that no one is waiting for me from a feedback standpoint.  ippinto your solution was awesome once I transposed rows and columns in my spreadsheet.  Thanks~
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35711309
So if mine does not work and you are no more interested in getting it to work the it would be appropriate to reopen the question and award points to jppinto. If you are interested in getting my solution to work the tell me more about the error and I shall try to help.

Saqib
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35711426
You can try this modification, You will not have to transpose your data

Saqib
Sub Macro1()
Set sh = ActiveSheet
For i = 2 To 51
    sh.Activate
    Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$1,Sheet1!$A$" & i & ":$H$" & i)
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = sh.Cells(i, 1)
Next i
    sh.Activate
    Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$H$50")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveSheet.Name = "All combined"
    ActiveChart.PlotBy = xlRows
End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:jppinto
ID: 35712380
@BobbaBuoy: Yes, I'm waiting for feedback from you! If you say that my solution works and ssaqibh didn't work, I think that you should have accepted my solution, instead of one that doesn't work! You should re-open the question and accept the correct one, OK?
0
 

Author Comment

by:Bob Schneider
ID: 35715158
How do I re-open it?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35715861
At the bottom right of your question there is a link which says "Request Attention"

Click on it and post a request to reopen the question.
0
 

Author Closing Comment

by:Bob Schneider
ID: 35719120
I really want to thank iipinto and ssagibh for their assistance in this.  They both gave me quality solutions that worked very well.  I also want to apologize for any "issues" I created by prematurely accepting a solution.

Thanks to both for their "expert" assistance!
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35719127
No wories...thanks!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35719181
You have again done it wrong. This is not about providing assistance. This is about solutions.

You have accepted a solution which does not work. If, at all, you should have selected #35711426 which is supposed to work.

Saqib
0
 

Author Comment

by:Bob Schneider
ID: 35719202
To me, it is about providing assistance via solutions to problems I am experiencing.  I pay a monthly fee for this service and, I believe, am free to use it in this manner.  There are usually more than one way to solve a problem (ie: more than one effective solution).  It is an immense help to me to see different approaches.  I saw two very effective solutions and rewarded them both.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35719273
Yes but the one you selected does not work. It has its first two lines missing. The one I posted last works and should have been accepted. Let this one go but keep it in mind for future questions.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question