Unlocking Cells

[lj]

Hi, I'd like to know if there's a simple way to unlock or lock cells.
I'm not sure if "Lock" is the correct term here. For example, if you
have a formula that has C$14. Is there an easy way to unlock (remove
the "$") from the formula or to ad it without manually doing it?
Thanks for your help!

Lisa

 

[Dave Peterson]

You can use a macro to change the reference style (absolute vs relative), but
you can also do it manually.

Select the portion of the formula (just a single cell reference or the entire
formula if you want) and hit the F4 key to cycle through all 4 options.

 

[lisaj]

Thanks, this is helpful. What I'm trying to do is to change a whole
group of cells from relative to absolute. Is there a way to highlight
a whole group of cells and do that? How do I use a macro to do this?

 

[Dave Peterson]

First, each reference has 4 posibilities:

$A$1 (both column and row are absolute references)
$A1 (column is absolute, row is relative)
A$1 (column is relative, row is absolute)
A1 (both are relative)

So here are 4 different macros from Gord Dibben:

Option Explicit
Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next Cell
End Sub
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 Cell
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)
End If
Next Cell
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)
End If
Next Cell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
Then you'll have to select the range of cells to fix and then hit alt-F8 (to see
the macro dialogs), pick the one you want and run it.




Just select the range to fix and run Gord's macro for the reference style you
want.

 

[lisaj]

First, each reference has 4 posibilities:

$A$1  (both column and row are absolute references)
$A1   (column is absolute, row is relative)
A$1   (column is relative, row is absolute)
A1    (both are relative)

So here are 4 different macros from Gord Dibben:

Option Explicit
Sub AbsoluteCol()
Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula = Application.ConvertFormula _
                (Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
        End If
    Next Cell
End Sub
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 Cell
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)
        End If
    Next Cell
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)
        End If
    Next Cell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
Then you'll have to select the range of cells to fix and then hit alt-F8 (to see
the macro dialogs), pick the one you want and run it.

Just select the range to fix and run Gord's macro for the reference styleyou
want.







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks, this is perfect!!

 

[Dave Peterson]

Gord did nice work!

Thanks, this is perfect!!

 

[Paul Hyett]

Thanks, this is helpful. What I'm trying to do is to change a whole
group of cells from relative to absolute. Is there a way to highlight
a whole group of cells and do that? How do I use a macro to do this?

Where possible, I use 'replace' to do this.