Inserting absolutes

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

Guest

Is there a one or two button punch method of adding/removing absolutes ($) to all cell references in a lengthy formulas?
 
select the cell with the formula (F5 and type in the address), press F2,
press Shift and Home to
highlight the formula, press F4, for every press of F4 you can see the
difference, first is absolute all
addresses, second is absolute rows, third absolute columns and fourth all
relative reference

--

Regards,

Peo Sjoblom


Alex said:
Is there a one or two button punch method of adding/removing absolutes ($)
Click to expand...
to all cell references in a lengthy formulas?
 
Alex

With the cell selected, highlight in the formula bar the references to change
then hit F4 to toggle through the options.

If you want a VBA macro solution.....

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

I built a userform with four option buttons and attached it to my right-click
menu.

Gord Dibben Excel MVP
 
Back
Top