PDA

View Full Version : Graph Help?



josephemery
01-29-2006, 08:28 PM
Hey,



Hopefully some one can figure it out. I have gotten data from a server and separate to different sheets based on value. Each sheet has between 4 and 10 set of data that I need to graph. I recorded a Marco to get a idea how to graph it. I can use the macro but i would have to rewrite the code ten times and would make it clutter. I am trying to figure a way to loop through the data, But the way i select the data to graph will not work. well here it is my code for now





Charts.Add
ActiveChart.charttype = xlLine
ActiveChart.SetSourceData Source:=Sheets("sheet").Range("F12")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = Sheets("sheet").Range("d3:d200")
ActiveChart.SeriesCollection(1).Values = Sheets("sheet").Range("e3:e200")
ActiveChart.SeriesCollection(1).Name = 'graph Name"
ActiveChart.Location Where:=xlLocationAsObject, Name:="sheet"



i think i need to select the range different i tried to look around but i found a couple site and they selected the range by "!R4C3: R4C200" I try to this but VBA wouldnt accept it. I was hoping to use the R4C3 method since i could us a variable to loop through the different data sets.

Thanks for any isights or links to the apporite spots. later

XLGibbs
01-30-2006, 06:37 AM
Yes the series formulas need to be actually sent to the chart series as opposed to the VBA equivalents you have shown...

For example


ActiveChart.SeriesCollection(1).XValues =Sheets("sheet").Range("D3:D200")


Would need to be :

ActiveChart.SeriesCollection(1).XValues = "=" & Sheets("Sheet").name & "!D3:D200"



Before we help you with the loop, get at least 1 chart working correctly...then we can help show you how to make more of the formula dynamic and tie it to a loop....

josephemery
01-30-2006, 01:26 PM
Oh ok thanks for the info, i see the difference but I don?t under stand the difference at the moment. I will look into and get the idea where you are coming from. Thanks for the help and I will look into it to see where I go from here.

josephemery
02-01-2006, 02:44 PM
Hello,

I been trying to figure it out but no luck yet. I cut and past the code in and it seems not to work out. I am not sure I been looking around to find a solution. I thought I ask again to see if anybody can help me. Thanks

mdmackillop
02-01-2006, 02:48 PM
Hi Joseph,
Can you sanitize and post a copy of your workbook?

tpoynton
02-01-2006, 03:48 PM
I would also suggest creating a graph in your workbook "the old fashioned way" so people know what you would like to end up with

josephemery
02-01-2006, 04:10 PM
Hello,



I just want to thank everybody before hand even before any help is given. Well I put a graph on the sheet the way I want it to come out I just want a graph or graphs for each data set. Well hopefully everybody can follow my code, I started to write this before I new the right way to write code so everybody can understand it. Well Thank for any advice or comments on the code. Well just, hit the FLO button on the info sheet to make it run. Thanks



Joseph

mdmackillop
02-01-2006, 04:37 PM
Hi Joseph,
Still looking at this but here's a start

ActiveChart.SeriesCollection(1).XValues = Sheets(p(b)).Range("B3:B200")
ActiveChart.SeriesCollection(1).Values = Sheets(p(b)).Range("C3:C200")


This will correct the basic error.

If you add Application.Screenupdating=False at the start of your code and Application.Screenupdating=True at the end, your code will run a lot faster.

JonPeltier
02-02-2006, 07:46 PM
This page might help you get a handle on VBA for charting:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html