Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Selected cells references
Hi,
I have a large spreadsheet that I need to change a large amount of data from a relative cell reference to an absolute cell reference. Is there a way for me to select the cells at one time and change the reference? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Selected cells references
Hi,
you can use replace, CTRL + H, find what i.e. A1:A100, replace with $A$1:$A$100, replace all "C" wrote: Hi, I have a large spreadsheet that I need to change a large amount of data from a relative cell reference to an absolute cell reference. Is there a way for me to select the cells at one time and change the reference? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Selected cells references
Take your pick from these.
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 Gord Dibben MS Excel MVP On Fri, 12 Mar 2010 08:31:01 -0800, C wrote: Hi, I have a large spreadsheet that I need to change a large amount of data from a relative cell reference to an absolute cell reference. Is there a way for me to select the cells at one time and change the reference? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change relative to absolute references in a block of cells | Excel Discussion (Misc queries) | |||
How do i change the color of the selected cells? | Excel Discussion (Misc queries) | |||
Can a workbook be locked against change to cells containing references to other files? | Excel Worksheet Functions | |||
How do I lock selected cells, so only I can change them? | Excel Worksheet Functions | |||
covert only cell references in selected cells into value leaving the existingcell formula | Excel Programming |