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.

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.

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 <(E-Mail Removed)> 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.