• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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?
0
juschuma
Asked:
juschuma
  • 3
  • 3
1 Solution
 
iaminitCommented:
Use Edit|Replace.
Select your cells.
Replace $ with nothing.
Then replace A with A$.
0
 
juschumaAuthor Commented:
My 1st post was an example.  In reality, there will be numerous cells with formulas.  Not all of them using Column A.
0
 
fugacityCommented:
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
 


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
fugacityCommented:
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.
0
 
fugacityCommented:
*yawn* yes i'm bored on a slow day...

Here's the macro changed to exactly what you want...changing formulas in all selected cells to use relative column and absolute row referencing.


Sub ChangeSelectedFromA1toR1C1AbsRow()
'
    Dim strInputFormula As String
    Dim strOutputFormula As String
    Dim rngTarget As Range
   
   
   
    For Each cell In Selection
   
    Set rngTarget = cell
   
    '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
   
    Next cell
End Sub
0
 
juschumaAuthor Commented:
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?
0
 
juschumaAuthor Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now