Microsoft KB Archive/70096

= Using FORMULA.CONVERT to Change Relative/Absolute References =

Article ID: 70096

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 4.0c
 * Microsoft Excel 3.0a
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q70096



SUMMARY
You can use the FORMULA function with the FORMULA.CONVERT function to change every reference in a selected area from relative to absolute reference. You can do this by using the FORMULA.CONVERT function to calculate the first argument, formula_text, of the FORMULA function.

However, because the FORMULA function will fail if you attempt to use A1-style references, you need to make sure that the FORMULA.CONVERT function is returning an R1C1-style formula and not an A1-style formula.



MORE INFORMATION
The third argument of the FORMULA.CONVERT function, To_a1, determines whether an A1-Style or R1C1-style reference is returned.

Example
The following macro is a short example that will convert all the references in the currently selected area into absolute references.

NOTE: This macro assumes that the references in the selected area are currently in A1-style. If you want to select an area that contains relative references in R1C1-style, change the second argument of the FORMULA.CONVERT function from TRUE to FALSE. A1: =FOR.CELL(&quot;curcell&quot;,,TRUE) A2: =FORMULA(FORMULA.CONVERT(GET.CELL (6,curcell),TRUE,FALSE,1,curcell),curcell) A3: =NEXT A4: =RETURN
 * 1) The FOR.CELL function starts a For.Cell-Next loop that will loop through the currently selected area. &quot;curcell&quot; is just the ref_name given to the new cell through each loop.
 * 2) The second line converts each reference in the selected range to an absolute references in R1C1-style, and then places it back into the current cell of the selection. If you wanted the macro to change absolute references to relative references, you would replace the fourth argument of the FORMULA.CONVERT function from 1 to 4. You must also include curcell as noted above otherwise, it will convert references based directly on the macro sheet rather than the worksheet cells.

NOTE: The third argument of the FORMULA.CONVERT function must be FALSE to use the FORMULA.CONVERT function to return the formula_text argument to the FORMULA function. If this third argument is TRUE, or if the formula_text argument uses A1 style references and this third argument is omitted, you will receive a macro error.

For an example of how to perform this function using a Visual Basic for Applications macro, click the article number below to view the article in the Microsoft Knowledge Base:

116028 XL: VB Macro to Change Between Relative/Absolute References

