Changing Cell References in Formulas
 

Win XP/Excel 2002

You guys are great!

So, two question: Is there a way to globally replace certain numbers within
a formula.

2nd: Without editing each formula, etc - is there a function that I can
make the formulas cell references absolute?

Thanks!

Pat Joram

Hi
1). Why not use 'Edit - Replace'
2) a repost from Gord Dibben:

----------------------------

Only through VBA macro.

Here are four...........

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)
End If
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)
End If
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)
End If
Next
End Sub

If not familiar with VBA and macros, see David McRitchie's site for
more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic
Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save
the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.


--
Regards
Frank Kabel
Frankfurt, Germany
"Pat" schrieb im Newsbeitrag
...
Win XP/Excel 2002

You guys are great!

So, two question: Is there a way to globally replace certain numbers
within
a formula.

2nd: Without editing each formula, etc - is there a function that I can
make the formulas cell references absolute?

Thanks!

Pat Joram

You can do editreplace to replace parts of a formula

To change from relative to absolute you need a macro unless you want to edit
each cell, press Alt+ F11, click insert module and paste in the following

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub

press alt + Q to close the editor, sabve the workbook, select the range you
want to change, do alt + F8 and run the macro (you can also put it in the
personal.xls to make it available for all workbooks)

Regards,

Peo Sjoblom

"Pat" wrote:

Win XP/Excel 2002

You guys are great!

So, two question: Is there a way to globally replace certain numbers within
a formula.

2nd: Without editing each formula, etc - is there a function that I can
make the formulas cell references absolute?

Thanks!

Pat Joram