Shortcut to change change cell reference to Absolute reference?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Once I create a formula and have tested it successfully is there a shortcut
to change some of the cell references to be absolute references or do I have
to arrow over and enter a $?
 
You can highlight the formula in the formula bar and press F4, and each
press will toggle from absolute, to absolute rows and relative columns,
absolute column and relative rows and finally back to relative
 
This is a great trick. However, what do you do if you have 3,250 cells you
want to convert to absolute? Do I have to click on each cell, click in
formula bar and hit F4 in all 3,250 cells?
 
You could use this macro (but make sure to read the note at the end)...

Sub ConvertAllReferencesToAbsolute()
Dim S As Range, C As Range, A As Variant, Addresses() As String
For Each S In Selection
If S.HasFormula Then
S.Formula = Replace(S.Formula, "$", "")
Addresses = Split(S.Precedents.Address, ",")
For Each A In Addresses
For Each C In Range(A)
S.Formula = Replace(S.Formula, C.Address(False, _
False), C.Address(True, True))
Next
Next
End If
Next
End Sub

Note: If any of your formulas have text constants in them that contain $
signs, those $ signs will be removed; so, you can't use the above on cells
with text constants containing $ signs. There is a way around this problem,
but it makes the macro take longer to execute do to the overhead associated
with the needed additional code.
 
Actually, maybe protecting text constants with $ signs in them is not all
that inefficient. Here is a macro to do that...

Sub ConvertAllReferencesToAbsolute()
Dim X As Long, S As Range, C As Range, A As Variant
Dim Parts() As String, Addresses() As String
For Each S In Selection
If S.HasFormula Then
Parts = Split(S.Formula, """")
For X = 1 To UBound(Parts) Step 2
Parts(X) = Replace(Parts(X), "$", Chr(1))
Next
S.Formula = Join(Parts, """")
S.Formula = Replace(S.Formula, "$", "")
Addresses = Split(S.Precedents.Address, ",")
For Each A In Addresses
For Each C In Range(A)
S.Formula = Replace(S.Formula, C.Address(False, _
False), C.Address(True, True))
Next
Next
S.Formula = Replace(S.Formula, Chr(1), "$")
End If
Next
End Sub

Oh, and I forgot to mention in my previously message... this macro works on
the Selected cells... if you have a defined range to work this code on, then
replace the Selection reference in this statement...

For Each S In Selection

with the specific range you want to use.
 
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.
 
ConvertFormula... I just **knew** there had to be a method for this... I
simply missed it when I went looking for it.

I would consider collapsing the four macros into a single subroutine that
would be called from one's own macro. Here is the subroutine...

Sub SetReferenceStyle(RefStyle As XlReferenceType)
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then Cell.Formula = _
Application.ConvertFormula(Cell.Formula, xlA1, xlA1, RefStyle)
Next Cell
End Sub

and you would call it like this...

SetReferenceStyle xlRelRowAbsColumn

where the argument choices are (use either the number or the predefined
constant, not both)...

1 -- xlAbsolute
2 -- xlAbsRowRelColumn
3 -- xlRelRowAbsColumn
4 -- xlRelative

Because I declared RefStyle as XlReferenceType, VB's Intellisense will
present these to you in a selectable drop down box when calling the
subroutine.
 
Run this macro after selecting all cells with the relative references.

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


Gord Dibben MS Excel MVP
 
And another change would be to only look at formulas in the selection:

dim myRng as range

set myrng = nothing
on error resume next
set myrng = intersect(selection, _
selection.cells.specialcells(xlcelltypeformulas))
on error goto 0

if myrng is nothing then
msgbox "Nothing to fix"
exit sub
end if

for each cell in myrng.cells
....

It could make a difference if there weren't many formulas in a large area--or if
the user selected whole columns...
 
Good idea! Simplifying the code a little bit...

Sub SetReferenceStyle(RefStyle As XlReferenceType)
Dim Cell As Range
On Error Resume Next
For Each Cell In Intersect(Selection, Selection.Cells. _
SpecialCells(xlCellTypeFormulas))
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, RefStyle)
Next Cell
End Sub
 
Back
Top