Change Cell Reference between Absolute and Relative

Question

I want to write a macro which goes through all cell references in a selection's cells formula and changes them to absolute or relative.

Is there a formatting variable which can change this or a function which does this already (similar to what pressing F4 does) but as a macro.

Wil it not always be absolute when you are looping through a selection of cells? or did I misunderstand your question? — Siddharth Rout, Aug 08 '16 at 05:39
@SiddharthRout It's more to do with adjusting the formula in the cell itself so it says $A$1 instead of A1 in all cases within the selection — AER, Aug 08 '16 at 05:47
A formula in a cell can have multiple ranges with multiple formats. Do you want to change all of them? For example `Vlookup($A$1,B10:B30,1,0)` — Siddharth Rout, Aug 08 '16 at 05:54
Yes, so the above with the Abs formula would be `VLOOKUP($A$1,$B$10:$B$30,1,0)` and with the Rel formula `VLOOKUP(A1,B10:B30,1,0)` — AER, Aug 08 '16 at 05:56
I have a very complex solution but want to check something with you. When your formula has mixed references as I have shown above with `vLookup` then how will you decide which one do you want? Absolute or Relative? — Siddharth Rout, Aug 08 '16 at 06:09
@SiddharthRout Thanks, I was just wanting to get the formula to change the reference. I feel bad because you put a lot of work into this but I accepted the first answer because I feel it would also answer the questions more specifically for people with a similar query. I upvoted your answer as it is a good solution though. — AER, Aug 10 '16 at 03:36

cyboashu · Accepted Answer · 2016-08-08T05:50:37.617

7

You can use ConvertFormula method.

4th Parameter determines whether its absolute or not. 1 sets it to absolute and 4 sets it to relative. As per one comment to this answer, if you are looking for mixed references, then its bit complex. but reading your question and comments, I think that's not what you are after.

Examples:
'/ Set it to absolute
ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, 1)

'/ Set it to relative
ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, 4)

edited Aug 08 '16 at 05:50

answered Aug 08 '16 at 05:25

cyboashu

10,196
2
27
46

This is part of the answer, how he can decide whether he had absolute or relative references? (What about formulas with mixed references? ) – Máté Juhász Aug 08 '16 at 05:27
1

++ Now you need to add just one check to see what kind of formula is it and then use one of the code that you provided – Siddharth Rout Aug 08 '16 at 06:00
This works within the workbook however not when the reference is between workbooks. If it can be amended to work with that I'll accept the answer. If there is no other way I'll just accept it though as this is effectively what I was looking for but does not work in my context. Many thanks. – AER Aug 08 '16 at 07:32

score 2 · Answer 2 · edited May 23 '17 at 12:22

I see you have edited the question but since I have already worked on this, I am posting an answer.

If you do not know what the formula contains and want to change Relative to Absolute and Absolute/Mixed to Relative then try this

Let's say I have 4 ranges in my Selection as shown below

So I can use RegEx as suggested Here to extract individial addresses and find what kind of formula is it and then do the changes as suggested by @cyboashu

Const sPattern As String = _
"(['].*?['!])?([[A-Z0-9_]+[!])?(\$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)?|\$?[A-Z]+:\$?[A-Z]+|(\$?[A-Z]+\$?(\d)+))"

Sub Sample()
    Dim sMatches As Object, objRex As Object
    Dim rng As Range, aCell As Range
    Dim sFormula As String
    Dim bAbsMix As Boolean, bRel As Boolean

    Set rng = Selection

    Set objRex = CreateObject("VBScript.RegExp")

    With objRex
        .IgnoreCase = True
        .Global = True
    End With

    For Each aCell In rng
        objRex.Pattern = """.*?"""
        sFormula = aCell.Formula
        sFormula = objRex.Replace(sFormula, "")

        objRex.Pattern = "(([A-Z])+(\d)+)"
        objRex.Pattern = sPattern

        If objRex.test(sFormula) Then
            Set sMatches = objRex.Execute(sFormula)
            If sMatches.Count > 0 Then
                For Each Match In sMatches
                    If Len(Match) = Len(Replace(Match, "$", "")) Then
                        bRel = True
                    Else
                        bAbsMix = True
                    End If
                Next Match
            End If
        End If

        If bAbsMix = True Then  '<~~ It is Absolute/Mixed
            Debug.Print sFormula & " in " & aCell.Address & " is Absolute/Mixed"
            aCell.Formula = Application.ConvertFormula(aCell.Formula, xlA1, xlA1, 4)
        Else '<~ It is Relative
            Debug.Print sFormula & " in " & aCell.Address & " is Relative"
            aCell.Formula = Application.ConvertFormula(aCell.Formula, xlA1, xlA1, 1)
        End If

        bRel = False: bAbsMix = False
    Next aCell
End Sub

In Immediate Window

Yes, indeed. Thank you for your effort. – AER Aug 08 '16 at 07:33 — AER, Aug 08 '16 at 07:33

Change Cell Reference between Absolute and Relative

2 Answers2

Linked