Results 1 to 3 of 3

Thread: Applying absolute cell referencing to many cell in Excel

  1. #1
    Join Date
    Jan 2011
    Posts
    12

    Applying absolute cell referencing to many cell in Excel

    Hi, 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. Any help is greatly appreciated. Thanks.

  2. #2
    Join Date
    Jul 2011
    Posts
    623

    Re: Applying absolute cell referencing to many cell in Excel

    I tried a simple formula: =A1+A2+A3+A4. I selected all cell refs and pressed F4. All of the refs turned to absolute. This is as far as changing the references within *a single formula*. If you change the refs in a single formula and then copy this cell, the copied cell refs will inherit the refs of the original formula.

  3. #3
    Join Date
    Jul 2011
    Posts
    634

    Re: Applying absolute cell referencing to many cell in Excel

    Here are a few macros for you to choose from.Select your range of cells and run the macro.
    Code:
    Sub Absolute()
    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
    End Sub
    
    Sub AbsoluteRow()
    Dim cell As Range
    For Each cell In Selection
    If cell.HasFormula Then
    cell.Formula = Application.ConvertFormula _
    (cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
    Next
    End Sub\
    
    Sub AbsoluteCol()
    Dim cell As Range
    For Each cell In Selection
    If cell.HasFormula Then
    cell.Formula = Application.ConvertFormula _
    (cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
    Next
    End Sub
    
    Sub Relative()
    Dim cell As Range
    For Each cell In Selection
    If cell.HasFormula Then
    cell.Formula = Application.ConvertFormula _
    (cell.Formula, xlA1, xlA1, xlRelative)
    Next
    End Sub

Similar Threads

  1. How to disable excel for taking empty cell in Excel chart
    By Hache hi in forum MS Office Support
    Replies: 2
    Last Post: 25-02-2012, 12:30 PM
  2. Cell format errors appears in Excel 2003 but not in Excel 2000
    By @nki$achi in forum MS Office Support
    Replies: 1
    Last Post: 24-01-2012, 05:00 PM
  3. Replies: 2
    Last Post: 04-01-2012, 06:54 PM
  4. Auto-populate a MS Word table cell with text from a diff cell?
    By dreamrthts in forum Windows Software
    Replies: 5
    Last Post: 23-03-2009, 10:39 PM
  5. Cell referencing in VBA
    By Antonio00 in forum MS Office Support
    Replies: 2
    Last Post: 08-02-2008, 11:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,675,687,952.43855 seconds with 17 queries