I came across a post today which was asking for assistance to convert all the for converting all of a selected range to either relative or absolute referencing.
The poster at first tried to choose the Specialcells method (a snipers rifle if you will) but could not get it working so checked all the cells in a selected range (a shot gun by comparison). The reason why the first method would be better is Excel will surgically concentrate on only the cells that have formula in and ignore the rest of the cells. The latter method will check all of the cells in the selected range.
The following are the values that need to be entered into the input box.
xlAbsolute 1 Convert to absolute row and column style.
xlAbsRowRelColumn 2 Convert to absolute row and relative column style.
xlRelRowAbsColumn 3 Convert to relative row and absolute column style.
xlRelative 4 Convert to relative row and column style (no references)
The following is the code I wrote to solve the problem. It works nicely.
Option Explicit
Sub ConvertFormulasToAbsolute()
Dim rng As Range
Dim i As Integer
i = InputBox("Add a number between 1 & 4", "Goski")
For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
rng.Formula = Application.ConvertFormula(rng.Formula, 1, 1, i)
Next
End Sub
Place the VBA code in a regular module and select the range you wish to affect.
All the very best.
Smallman
