Microsoft KB Archive/67454

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

-

SUMMARY
To manipulate a Microsoft Excel chart's SERIES formula with a macro, the GET.FORMULA(reference) command can be used to return the entire SERIES formula as text. The resultant text string can then be manipulated with Excel's text functions.

When the &quot;reference&quot; argument of the GET.FORMULA command is entered in the form &quot;S#P#&quot; (Series#Point#), the SERIES formula for the specified series and point number will be returned.

For example, if a chart is created from the values in cells A1:A5 on a worksheet and information is added to cell A6, the following macro will update the existing chart to include the new information:

A1: AddRow

A2: =GET.FORMULA(&quot;S1P1&quot;)

A3: =LEFT(A2,SEARCH(&quot;:&quot;,A2)+1)

A4: =SEARCH(&quot;:&quot;,A2)+2

A5: =SEARCH(&quot;C&quot;,A2,A4)

A6: =A5-A4

A7: =MID(A2,A4,A6)+1

A8: =RIGHT(A2,LEN(A2)-(SEARCH(&quot;:&quot;,A2)+1+A6))

A9: =A3&A7&A8

A10: =SELECT(&quot;S1P1&quot;)

A11: =FORMULA(A9)

A12: =RETURN

MORE INFORMATION
 A1 = Macro name. A2 = Returns the SERIES formula as text from series 1, point 1 on     the active chart. A3 = Returns the left side of the formula, up to the row number to     be incremented. A4 = Returns the position of the row number to incremented. If the row number has more than one digit, returns the first digit. A5 = Returns the position of the character &quot;C&quot;, which will follow the row number to be incremented. A6 = Returns the number of digits in the row number. A7 = Identifies the entire row number and adds one to it. A8 = Returns the right side of the formula, beginning at the first non-digit after the row number. A9 = Concatenates the left side of the formula, new row number, and right side of the formula. A10 = Selects series 1, point 1 on the active chart. A11 = Enters the new formula into the formula bar, replacing the old formula. A12 = Ends the macro.