Share via

Apply Absolute Reference to Multiple Cells at Once

Anonymous
2013-12-01T11:34:06+00:00

Hello,

Hope all is well.  I know that you press F4 to change the cell referencing to absolute, relative or mixed. But is there a way to select a range of cells to apply the change all at once instead of having to choose each one and pressing F4 for each.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-12-01T16:07:59+00:00

    Gord Dibben published this some time back and I've made minor mods to it just to add information, the heart of the code is his:

    Sub MakeAbsoluteAddresses()

    'Thanks to Gord Dibben for the code!

    'http://www.excelforum.com/excel-general/372383-making-multiple-cells-absolute-at-once.html

    'to insert this code into a regular code module, follow the instructions here:

    'http://www.contextures.com/xlvba01.html#videoreg

    'To Use:

    ' Select the cells to be converted to absolute addressing

    ' then run this macro.

     Dim Cell As Range

     For Each Cell In Selection

      If Cell.HasFormula Then

        Cell.Formula = _

         Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsolute)

      End If

     Next

     MsgBox "Conversion to Absolute completed.", vbOKOnly, "Job Done"

    End Sub

    100+ people found this answer helpful.
    0 comments
  2. Anonymous
    2013-12-03T18:11:47+00:00

    Thanks so much, but I've never written a marco before, how do I add this into my Personal macro workbook?

    Assuming you already have a Personal Macro Workbook.

    Is it loaded. . .probably hidden. . .right now when you start Excel?

    If so, hit Alt + F11 to go to VBEditor.    Select your personal workbook and expand to see Module1.

    Open that module and paste in Jerry's code.  Save Personal workbook.   Alt + q to return to Excel.

    Select the range to change then run the macro.

    If you don't have a Personal Macro Workbook post back for instructions or read Help on how to create one.

    Gord

    50+ people found this answer helpful.
    0 comments
  3. Anonymous
    2013-12-01T16:29:15+00:00

    To add to Jerry's post. . . . . .

    Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsolute)

    The four conditions are. . . . . . .

    xlAbsolute
    xlAbsRowRelColumn
    xlRelRowAbsColumn
    xlRelative

    Gord

    30+ people found this answer helpful.
    0 comments
  4. Anonymous
    2013-12-03T17:42:31+00:00

    Thanks so much, but I've never written a marco before, how do I add this into my Personal macro workbook?

    5 people found this answer helpful.
    0 comments