Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I change an Excel range of cells from relative to absolute.
I want to change, in a range of cells from relative cell adressing into
absolute cell adressing, not by changing each cel but change them all by one command. |
#2
|
|||
|
|||
You can use Edit/Replace. If the range is fixed it's even better because there's less risk of hosing something unintentionally. For range A1:D10 Under Edit/Replace: Find What = A: Replace With = $A: Find What = :D Replace With = :$D Or, if the range is fixed for all, then Find What = A1:D10 Replace with = $A$1:$D$10 -- wmjenner ------------------------------------------------------------------------ wmjenner's Profile: http://www.excelforum.com/member.php...fo&userid=5282 View this thread: http://www.excelforum.com/showthread...hreadid=278751 |
#3
|
|||
|
|||
Hi
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. Gord Dibben Excel MVP ------------------------------- Jrhenk wrote: I want to change, in a range of cells from relative cell adressing into absolute cell adressing, not by changing each cel but change them all by one command. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I merge in a selected range of cells out of Excel? | Excel Discussion (Misc queries) | |||
linking cells in Excel 2003. How to not truncate to 255 characters. | Excel Discussion (Misc queries) | |||
How do you change the size of a range of cells in a column/row | Charts and Charting in Excel | |||
stop excel from shifting cells up when a query returns no data | Excel Worksheet Functions |