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:
[URL="http://www.contextures.com/xlvba01.html"]http://www.contextures.com/xlvba01.html[/URL]
David McRitchie has an intro to macros:
[URL="http://www.mvps.org/dmcritchie/excel/getstarted.htm"]http://www.mvps.org/dmcritchie/excel/getstarted.htm[/URL]
Ron de Bruin's intro to macros:
[URL="http://www.rondebruin.nl/code.htm"]http://www.rondebruin.nl/code.htm[/URL]
(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 wrote:
>
> 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?
>
> On Mar 23, 11:50 am, Dave Peterson wrote:
> > 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.
> >
> > lj wrote:
> >
> > > 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 >> Stay informed about: Unlocking Cells 
FAQ
Search
Profile
Private Messages
Log in
