asked on

How to change the reference for a selection.

Is there VBA code to change all the formulas references in a selection?

For example, if my selection includes three cells:

=A1
=$A$1
=$A1

I want to select all the cells and run a macro that changes all of them to the same reference style:

=A$1
=A$1
=A$1

What's the best way to do this?

iaminit

Use Edit|Replace.
Select your cells.
Replace $ with nothing.
Then replace A with A$.

juschuma

ASKER

My 1st post was an example. In reality, there will be numerous cells with formulas. Not all of them using Column A.

fugacity

use the function ConvertFormula. It's in VBA help. I show it used in a sub below; looping through cells is left to you.
Note that the fromReferenceStyle parameter MUST be correct or you will get a 1004 error when you try to send outputformula back to the range.

After the sub I list some other properties that may be useful. Again, they're in VBA help

Sub ChangeFromA1toR1C1AbsRow()
'
Dim strInputFormula As String
Dim strOutputFormula As String
Dim rngTarget As Range

Set rngTarget = Range("D4")

'get original formula
strInputFormula = rngTarget.Formula
' convert to R1C[1] format
strOutputFormula = Application.ConvertFormula( _
Formula:=strInputFormula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1, _
toabsolute:=xlAbsRowRelColumn)
' enter new formula
rngTarget.Value = strOutputFormula

End Sub

Range.FormulaR1C1Local
Range.FormulaR1C1

fugacity

Hotkey...hitting F4 in excel toggles the reference for you.

I'm pasting the help topic for the function I used above in case you don't have VBA help installed.

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

Syntax

expression.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)

expression Required. An expression that returns an Application object.

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 Long. The reference style of the formula. Can be one of the following XLReferenceStyle constants: xlA1 or xlR1C1.

ToReferenceStyle Optional Variant. The reference style you want returned. Can be one of the following XLReferenceStyle constants: xlA1 or xlR1C1. If this argument is omitted, the reference style isn't changed; the formula stays in the style specified by FromReferenceStyle.

ToAbsolute Optional Variant. Specifies the converted reference type. Can be one of the following XLReferenceType constants: xlAbsolute, xlAbsRowRelColumn, xlRelRowAbsColumn, or xlRelative. 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.

ASKER CERTIFIED SOLUTION

fugacity

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

juschuma

ASKER

Thanks for the posting. This will help quite a bit. I modified the formula and am getting some strange results. Here's the code I'm using:

For Each cell In Selection

cell.Formula = Application.ConvertFormula( _
Formula:=cell.Formula, _
fromReferenceStyle:=xlA1, _
toabsolute:=xlRelRowAbsColumn)

Next cell

It works fine if I'm using xlAbsolute, but when it refers to a relative Row, the formulas get screwed up.

=A1
=A2
=A3
=A4

Turns into:

=$A1
=$A3
=$A5
=$A7

My guess is it has to do with the last optional parameter, "RelativeTo", but I need this macro to be dynamic. For example one of the cells might be:

=A1 + B4 (i.e. referring to two different cells)

Any thoughts?

juschuma

ASKER

Figured it out...it works if I change it to:

For Each cell In Selection

cell.Formula = Application.ConvertFormula( _
Formula:=cell.Formula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, _
toabsolute:=xlRelRowAbsColumn)

Next cell

Adding the toReferenceStyle line makes it work. Thanks.