?
Solved

How to change the reference for a selection.

Posted on 2003-03-14
7
Medium Priority
?
296 Views
Last Modified: 2012-05-04
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
Comment
Question by:juschuma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 1

Expert Comment

by:iaminit
ID: 8138156
Use Edit|Replace.
Select your cells.
Replace $ with nothing.
Then replace A with A$.
0
 

Author Comment

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

Expert Comment

by:fugacity
ID: 8138544
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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Expert Comment

by:fugacity
ID: 8138559
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
 

Accepted Solution

by:
fugacity earned 260 total points
ID: 8138683
*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
 

Author Comment

by:juschuma
ID: 8139484
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
 

Author Comment

by:juschuma
ID: 8139586
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question