Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How can I get underlying range when Series Formula is longer than 255characters?
I am trying to write a macro that will navigate to the range that a
series represents. I do this by parsing Series.Formula. however, if the Series.formula is longer than 255 chars, it gets truncated. Is there any way to get the range in a situation like this? Here is some code that generates a problem chart: Sub CreateChartWithTrickyFormula() ' ' Macro2 Macro ' Macro recorded 12/4/2007 by saldanha ' Range("A1:A34").FormulaR1C1 = "=RAND()" Range("C1:C34").FormulaR1C1 = "=RAND()" Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SeriesCollection(1).XValues = _ "={38408,38436,38467,38497,38528,38558,38589,38620 ,38650,38681,38711,38742,38773,38801,38832,38862,3 8893,38923,38954,38985,39015,39046,39076,39107,391 38,39166,39197,39227,39258,39288,39319,39350,39380 ,39411,39441,39472,39503}" ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C1:R34C1" ActiveChart.SeriesCollection(1).Name = _ "=""BigLongChartSeriesThatWillOverFlowWhenGottenAs Formula""" ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = _ "BigLongFormulaThatWillOverflowWhenGottenAsPropert y" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub If you now type the following in the immediate window, you will find that the formula is 255 chars, and does not contain the range R1C1:R34C1 ?len(activechart.SeriesCollection.item(1).formula) ?activechart.SeriesCollection.item(1).formula |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
editing formula no longer outlines ranges | Excel Worksheet Functions | |||
How do I delete the name for a named range i no longer require? | Excel Worksheet Functions | |||
x y scatter chart series ranges reset to x(range) = y(range) | Charts and Charting in Excel | |||
what is the underlying formula for the percentrank function | Excel Worksheet Functions | |||
How do I change a range name back to the underlying data range? | Excel Worksheet Functions |