User Name
Password

Go Back   Planetarion Forums > Non Planetarion Discussions > Programming and Discussion

Reply
Thread Tools Display Modes
Unread 7 Jul 2004, 19:51   #1
Entium
Registered User
 
Entium's Avatar
 
Join Date: Dec 2001
Location: Confœderatio Helvetica
Posts: 323
Entium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud of
Excel; Graph automating

Ok, I have an Excel-sheet with data of many enterprises.
It looks like this:

Code:
 
    
        Data1|Data2|Data3|Data4...   Data n-1|Data n
Firm1___|_____|____|__________________________           
Firm2___|_____|____|___________________________
Firm3___|_____|____|___________________________
.
.
.
Firm n-1____________________________________
Firm n______________________________________
___________________________________________
Median
___________________________________________
Mean Value
___________________________________________
I need excel to draw a graph with blocks of data (say Data1-3; Data4+5) for every firm and include the statistical parts (Median etc...).
The problem is that I have too many firms/to much data on them to link them for every graph manually.
So I need some kind of automatisation for it, say I can specify the data block and Excel draws a graph for every firm on the corresponding data. Or I can specify all data blocks and Excel draws graphs for the firm I want. Or - preferably - both at the same time.


Any ideas how to do this?

Thanks.
Entium is offline   Reply With Quote
Unread 7 Jul 2004, 20:15   #2
JetLinus
Friendly geek of GD :-/
 
JetLinus's Avatar
 
Join Date: Nov 2000
Location: On my metal roid
Posts: 923
JetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud of
Lightbulb Re: Excel; Graph automating

Ouh, this really is a shit task to do.
It can be done using macros.
But I'm afraid, recording it won't be enough, you'll have to do some coding by hand.

Well, try this:
  1. Make sure nothing is selected on your data worksheet.
  2. Record a macro (extras, macros, record, start)
  3. Select the cells you need to create the graph
  4. Actually create the graph
  5. Stop recording the macro
  6. You'll be taken to the VBA Editor. Now the nice bit starts ^^

Well, you need to modify the beginning, where the cells which hold the source data for your graph are selected. This needs to be adapted so that various selections will work.
You could make the macro a common sub-routine / function, which accepts a given range/selection (of cells) as argument.
Then, somewhere else, cycle through all possible selections (i.e. enterprises), and pass them to this macro-function.
Also don't forget to make sure that the new graph will be created seperately, so that the last one doesn't get overwritten.

Wow, sounds really nasty this thing, it's the sort of office work I don't like ^^

And you're probably going to HATE Mr. PaperClip (but you'll pretty much rely on him)
__________________
[»] Entropy increases! :-/
JetLinus is offline   Reply With Quote
Unread 7 Jul 2004, 23:19   #3
Entium
Registered User
 
Entium's Avatar
 
Join Date: Dec 2001
Location: Confœderatio Helvetica
Posts: 323
Entium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud of
Re: Excel; Graph automating

Thanks, glad to hear that it's possible, technically...

I'll give it a go tomorrow.

Last edited by Entium; 7 Jul 2004 at 23:27.
Entium is offline   Reply With Quote
Unread 17 Jul 2004, 17:41   #4
Entium
Registered User
 
Entium's Avatar
 
Join Date: Dec 2001
Location: Confœderatio Helvetica
Posts: 323
Entium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud of
Re: Excel; Graph automating

Any idea how the exact code could look like, that increases the row/row range every loop (<U1>, ---> <U2>), , without altering the relation to the other graph series, i.e. the statistical parts?

Code:
Charts.Add
    ActiveChart.ChartType = xlRadarMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(<U1>), _
        PlotBy:=xlRows
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).Values = "=Sheet1!<Median>"
...
<loop>

Charts.Add
    ActiveChart.ChartType = xlRadarMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(<U2>), _
        PlotBy:=xlRows
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).Values = "=Sheet1!<Median>"
And does the 'Range'-Value include the rows of the statistical data too, or do I have to add them as 'NewSeries' as shown above?
Entium is offline   Reply With Quote
Unread 18 Jul 2004, 05:02   #5
Entium
Registered User
 
Entium's Avatar
 
Join Date: Dec 2001
Location: Confœderatio Helvetica
Posts: 323
Entium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud ofEntium has much to be proud of
Re: Excel; Graph automating

For future reference, I think I got it working.

I changed the table that it looks like this:

Code:
_________Data1_|_Data2_|_ (Columns B to I)
Min. (row 2)
1.Quartile
Median
2.Quartile
Max
_________
Firm1 (row 7)
Firm2
.
.
.
The following code then draws a new graph for every firm with the corresponding statistical values:

Code:
Sub Autodraw()
'
' Autodraw Macro
' Macro recorded 18.07.2004 by 
'
Dim lRow As Long
  
Dim sSheet As String
  
Dim sGraph As String
  
Dim sSource As String
'
Application.ScreenUpdating = False
  
sSheet = ActiveSheet.Name
For lRow = 7 To ActiveSheet.UsedRange.Rows.Count
sSource = "A" & CStr(lRow) & ":I" & CStr(lRow)

Range(sSource).Select

Range("A" & CStr(lRow)).Activate
  
sGraph = ActiveCell.Value
  
Charts.Add
ActiveChart.ChartType = xlRadarMarkers
ActiveChart.SetSourceData Source:=Sheets(sSheet).Range(sSource), _
PlotBy:=xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R2C2:R2C9"
ActiveChart.SeriesCollection(2).Name = "=Sheet1!R2C1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "=Sheet1!R3C2:R3C9"
ActiveChart.SeriesCollection(3).Name = "=Sheet1!R3C1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Values = "=Sheet1!R4C2:R4C9"
ActiveChart.SeriesCollection(4).Name = "=Sheet1!R4C1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).Values = "=Sheet1!R5C2:R5C9"
ActiveChart.SeriesCollection(5).Name = "=Sheet1!R5C1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(6).Values = "=Sheet1!R6C2:R6C9"
ActiveChart.SeriesCollection(6).Name = "=Sheet1!R6C1"

ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.HasTitle = False

ActiveSheet.Name = sGraph
  
Sheets(sSheet).Activate
Next
End Sub
Entium is offline   Reply With Quote
Unread 18 Jul 2004, 11:11   #6
JetLinus
Friendly geek of GD :-/
 
JetLinus's Avatar
 
Join Date: Nov 2000
Location: On my metal roid
Posts: 923
JetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud ofJetLinus has much to be proud of
Arrow Re: Excel; Graph automating

Hey, nice
__________________
[»] Entropy increases! :-/
JetLinus is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Forum Jump


All times are GMT +1. The time now is 22:51.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2002 - 2018