making Excel graph splitter - need to extract info from series formula

[dnky]

Hi everybody,

i'm a novice trying to write a macro for a simple graph splitter. Just one that takes the graph you're looking at and divides it up into smaller ranges.

Currently i have it working on a simple loop. The problem is you have to manually input the start and end row numbers. Also you cant change the column placements without having to change the macro code.

I basically just want to be able to tell each series where to finish and end!

for example: "=SERIES(Data!$B$1,Data!$A$2:$A$7939,Data!$B$2:$B$7939,1)"

i replace with
"=SERIES(" & chartname & "!$B$1," & chartname & "!$A$" & startval & ":$A$" & endval & "," & chartname & "!$B$" & startval & ":$B$" & endval & "," & n & &quot"

the only problem is, startval and endval have to be manually input in and it's restricted to graphs that use columns A and B.

Is there anyway i can pick out the values 2 & 7939 and the columns A & B from that long code.....

or does anyone have any suggestions.

any help would be appreciated. If i get a fully working independent graph splitter that could operate as a button i will submit the entire code for others to use - i think it could be popular.

cheers

duncan

 

[RobBroekhuis]

If you don't want the columns and start/end rows to be manually input, where do you want this information to come from? You're most of the way there, I just can't tell what you really want to do.


Rob

 

[dnky]

sorry i wrote that last night after brainstorming on it for hours. rereading it this morning i realised it didn't make sense because i typed it in instead of just copying the code directly.

so here's what i have so far....

Sub FocusedChartSplitter()

Dim chartname As String
Dim startval As Integer
Dim endval As Integer
Dim splitval As Integer

chartname = ActiveChart.Name
startval = InputBox("enter first row", "Beginning of chart values", 2)
endval = InputBox("enter last row", "End of chart values&quot
splitval = InputBox("enter the number to divide by - e.g. 600 for 10 minutes of second data per graph", "Divideby&quot
numberval = (endval - startval) / splitval
numberwhole = Format(numberval, "0&quot
Message = MsgBox("This will produce " & numberwhole & " number of graphs", vbOKOnly)

For newchartloop = 1 To numberwhole
Sheets(chartname).Copy Before:=Sheets(chartname)
a = startval + (splitval * (newchartloop - 1))
b = startval + (splitval * newchartloop)
c = endval
If c < b Then b = c

' may need to renumber columns (Cn) so they are correct

ActiveChart.SeriesCollection(1).Formula = &quot;=SERIES(Data!R1C2,Data!R&quot; & a & &quot;C1:R&quot; & b & &quot;C1,Data!R&quot; & a & &quot;C2:R&quot; & b & &quot;C2,1)&quot;
ActiveChart.SeriesCollection(2).Formula = &quot;=SERIES(Data!R1C4,Data!R&quot; & a & &quot;C1:R&quot; & b & &quot;C1,Data!R&quot; & a & &quot;C4:R&quot; & b & &quot;C4,2)&quot;
ActiveChart.SeriesCollection(3).Formula = &quot;=SERIES(Data!R1C5,Data!R&quot; & a & &quot;C1:R&quot; & b & &quot;C1,Data!R&quot; & a & &quot;C5:R&quot; & b & &quot;C5,3)&quot;

Next newchartloop
End Sub

I have this working at the moment on some very lengthy data because the column positions are always the same.

I was hoping to get rid of the need for entering the startval and endval. And also make it universal for all line graphs. (with a loop to cover more or less than 3 series)

Thanks for your time on this.

duncan

 

[dnky]

p.s. in answer to your question (doh!!):

i'd like to take the values from the series formula that's already there. The graph exists already so can i extract these values from it?

e.g. series formula
&quot;=SERIES(Data!$B$1,Data!$A$2:$A$7939,Data!$B$2:$B$7939,1)&quot;

...can i extract the columns A and B, and row numbers 2 and 7939 from it?

 

[RobBroekhuis]

Right off, I noticed you're mixing .formula and .formulaR1C1. Make sure you use the right one! You can certainly parse the existing series formula to extract the row values. I find it easier to parse R1C1 formulas, e.g.

s=activechart.seriescollection(1).formulaR1C1
p=instr(s,&quot;,&quot ' find start of x range
p=instr(p+1,s,&quot;R&quot+1 'find start of row number
q=instr(p,s,&quot;C&quot 'find start of column number
startrownum=val(mid(s,p,q-p))

And similar code for the end row number


Rob

 

[dnky]

thats brilliant

i'd never seen the InStr and Mid statements before. (and i didn't know you could use .FormulaR1C1) - there's only so much you can learn from recording macros and looking at the code.

all i had to do was change the &quot;,&quot; to a &quot;:&quot; for the end row number and now i understand it i can use it to get the column numbers and even the data sheet name!!

big big big thanks

duncan

 

[MichaelRed]

dinky,

InStr, Mid and MANY additional functions (statements) are the basis of 'programing' in VBA. To do the type of manipulation referenced in this thread, you will need at least a 'nodding acquaintance' with the topic. Ms. Excel in general and VBA (Excel) are well documented in numerous texts. I do not have any recent volumes on Excel specifically and generally aviod making suggestions on individual volumes, but I do suggest that you visit your local bookstore and browse the computer section for something which covers the topic, is at least mostly understandable to you and provides some material which challenges your current abilities.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[dnky]

That's a very good point MichaelRed. My main use of excel is doing data analysis of noise readings as a junior in an environmental health consultancy. I've only begun to explore replacing years of repetetive tasks of graph making with macros... and i've had a great deal of luck mostly thanks to the friendly experts on this forum. Finding the solution in a book would have likely cost me a lot more time and money. The time Rob took out to point me in the right direction has obviously saved me that. That said, a VBA book is now on my xmas list, and i'll try to not to be a pest in future.

Thanks again guys

duncan

 

[RobBroekhuis]

Don't worry - you're no pest (I know one when I see one )


Rob

 

[SkipVought]

duncan,

I havn't figgerd out yet exactly what you are driving at, but let me throw another dimension into the mix here.

You mentioned data analysis and there are several options to explore. One is PivotChart. I'll just throw that one to you for grins. The second is using the OFFSET function to name the ranges in columns A & B. By varying 2 of the arguments in the OFFSET function, you can

1) make a sliding window of
2) varying height (number of rows)

The result in your chart would be to be able to dynamically add data, vary the Chart starting point and vary the number of data points to view.

How's that sound?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com