Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jrhenk
 
Posts: n/a
Default 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   Report Post  
wmjenner
 
Posts: n/a
Default


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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
How do I merge in a selected range of cells out of Excel? powertots4 Excel Discussion (Misc queries) 1 December 28th 04 09:01 PM
linking cells in Excel 2003. How to not truncate to 255 characters. GarryFerg Excel Discussion (Misc queries) 5 December 8th 04 04:33 PM
How do you change the size of a range of cells in a column/row SOkoll Charts and Charting in Excel 1 December 8th 04 07:43 AM
stop excel from shifting cells up when a query returns no data DrLostinExcel Excel Worksheet Functions 2 November 9th 04 06:44 PM


All times are GMT +1. The time now is 02:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"