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.

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.

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.