Converting Formulas

Converts cell references in a formula between the A1 and R1C1 reference styles, between relative and absolute references, or both. Variant.

This example converts a SUM formula that contains R1C1-style references to an equivalent formula that contains A1-style references, and then it displays the result.

Dim sFormula As String 
Dim sChangedFormula As String 

sFormula = "=SUM(R10C2:R15C2)" 
sChangedFormula = Application.ConvertFormula (Formula:=sFormula, _ 
                                              FromReferenceStyle:=xlReferenceStyle.xlR1C1, _ 
                                              ToReferenceStyle:=xlReferenceStyle.xlA1) 

sChangedFormula = "=SUM($B$10:$B$15)" 

Formula - Required Variant. A string that contains the formula you want to convert. This must be a valid formula, and it must begin with an equal sign.
FromReferenceStyle - The reference style of the formula.
ToReferenceStyle - The reference style you want returned. If this argument is omitted, the reference style isn't changed; the formula stays in the style specified by FromReferenceStyle.
ToAbsolute - Specifies the converted reference type. If this argument is omitted, the reference type isn't changed.
RelativeTo - Optional Variant. A Range object that contains one cell. Relative references relate to this cell.

Dim sFormula As String 

sFormula = "=SUM(R10C2:R15C2)" 
Application.ConvertFormula (Formula:=sFormula, _ 
                            FromReferenceStyle:=xlReferenceStyle.xlR1C1, _ 
                            ToReferenceStyle:=xlReferenceStyle.xlA1, _ 
                            ToAbsolute:=xlReferenceStyle.xlR1C1, _ 
                            RelativeTo:=Range("D4") )