Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Changing Cell References in Formulas
|
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Formats in formulas | Excel Discussion (Misc queries) | |||
Changing cell references | Excel Discussion (Misc queries) | |||
Excel: how to formulate conditional cell references | Excel Worksheet Functions | |||
Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |