Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Question Unanswered: Stupid Question: Shortcut Key for a locked range

    Hi, I'm sorry but i can't find this anywhere.

    Imagine I am referencing a specific cell using =A1

    If I drag the formula in either direction this changes to =A2, =A3 or =B1, =C1...

    I know that the formula to lock this is =$A$1 but is there a way of selecting this locked range without having to modify the formula manually to add the $'s...

    I mean if I click a cell it writes A1 into the formula but if I hold the CTRL button whilst clicking it would select $A$1...

    If there is a solution, I've gone years without knowing this!

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    One way to manually enter an absolute reference formula is to type in the formula without any dollar signs, press F4, press Enter.
    For an existing formula, select the cell, drag your mouse over the text in the formula bar, and press F4 to
    cycle through the different reference states until you see the formula in the absolute (locked) state, then press Enter.

    It may be possible to automate this in code, and I am posting a demo to show how this might be done. I say "might"
    because the code I've developed in a few hours, that seems to work, has only been briefly tested. If the tested and
    debugged version were to be used, it cound be put in a sub in a module in Personal.xls (in the XLStart folder), assign
    a hot-key to run it, such as Ctrl+e, and you may save some time converting one or more cell formulas to absolute reference.
    This works for me in Excel 2003.
    Jerry
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I found an easier way to automate changing a cell reference in a formula from any state to absolute. I also learned that the
    first demo I attached did not include all scenarios, for example "=A$1", when entered in column #1, was not converted to absolute,
    and analyzing the formula's code string in VBA to do the conversion was, in my opinion, not possible.

    I found some code at a dotcom forum called ExcelForum. I revised my demo, which now has just a few lines of code.
    Jerry
    Code:
    Sub Lock_MyFormula()
    Dim xCell As Range
      
      For Each xCell In Selection.Cells
      
        If xCell.HasFormula Then
            xCell.Formula = Application.ConvertFormula(Formula:=xCell.Formula, _
                fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)
        End If
    
      Next
    
    End Sub
    
    Sub UnLock_MyFormula()
    Dim xCell As Range
      
      For Each xCell In Selection.Cells
      
        If xCell.HasFormula Then
            xCell.Formula = Application.ConvertFormula(Formula:=xCell.Formula, _
                fromreferencestyle:=Application.ReferenceStyle, toreferenceStyle:=xlA1, toabsolute:=xlRelative)
        End If
    
      Next
    
    End Sub
    Attached Files Attached Files
    Last edited by JerryDal; 07-28-12 at 00:09. Reason: clarification

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •