ConvertFormula Method
Converts cell references in a formula between the A1 and R1C1 reference styles, between relative and absolute references, or both. Variant.
expression.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)
expression Required. An expression that returns one of the objects in the Applies To list.
Formula Required Variant. A string that containis the formula you want to convert. This must be a valid formula, and it must begin with an equal sign.
FromReferenceStyle Required XlReferenceStyle. The reference style of the formula.
| XlReferenceStyle can be one of these XlReferenceStyle constants. |
| xlA1 |
| xlR1C1 |
ToReferenceStyle Optional XlReferenceStyle. 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.
| XlReferenceStyle can be one of these XlReferenceStyle constants. |
| xlA1 |
| xlR1C1 |
ToAbsolute Optional XlReferenceStyle. Specifies the converted reference type. If this argument is omitted, the reference type isn't changed.
| XlReferenceStyle can be one of these XlReferenceStyle constants. |
| xlAbsolute |
| xlAbsRowRelColumn
xlRelRowAbsColumn xlRelative |
RelativeTo Optional Variant. Optional Variant. A Range object that contains one cell. Relative references relate to this cell.
Example
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.
inputFormula = "=SUM(R10C2:R15C2)"
MsgBox Application.ConvertFormula( _
formula:=inputFormula, _
fromReferenceStyle:=xlR1C1, _
toReferenceStyle:=xlA1)