changing multiple cells from relative to absolute reference

Is it possible to change a large range of very complex formulas from relative
reference to absolute reference. Something other than the replace command?

thanks, Mike

Hi Mike

one option that might work if the cell references you want to replace are
used multiple times and that is to use range names, for example if you have
the formulas
=A1*A2
=A1*A5
=A1+A10
and you now want to make A1 absolute, select A1, click in the name box (box
to left of formula bar above column A) and type a name for the range e.g.
Rng_A1
and press ENTER (v. important)
now choose

insert / name / apply and click ok

you will end up with
=Rng_A1*A2
=Rng_A1*A5
=Rng_A1+A10

and range names are absolute references.

Please test this on a copy of your workbook first to see if it achieves what
you want.

Cheers
JulieD


"Mike" <Mike@discussions.microsoft.com> wrote in message
news:3CB541E1-8A7B-4BD3-B45D-D117D2270350@microsoft.com...
> Is it possible to change a large range of very complex formulas from
> relative
> reference to absolute reference. Something other than the replace command?
>
> thanks, Mike

You can do it with the following macro:

Sub ChangeFormulas()
Dim Rng As Range
For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas)
If Rng.HasArray Then
Rng.FormulaArray = Application.ConvertFormula( _
fromreferencestyle:=xlA1, Formula:=Rng.Formula,
toabsolute:=True)
Else
Rng.Formula = Application.ConvertFormula( _
fromreferencestyle:=xlA1, Formula:=Rng.Formula,
toabsolute:=True)
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Mike" <Mike@discussions.microsoft.com> wrote in message
news:3CB541E1-8A7B-4BD3-B45D-D117D2270350@microsoft.com...
> Is it possible to change a large range of very complex formulas
> from relative
> reference to absolute reference. Something other than the
> replace command?
>
> thanks, Mike

Chip, thanks for the macro, i'll give it a shot.

"Chip Pearson" wrote:

> You can do it with the following macro:
>
> Sub ChangeFormulas()
> Dim Rng As Range
> For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas)
> If Rng.HasArray Then
> Rng.FormulaArray = Application.ConvertFormula( _
> fromreferencestyle:=xlA1, Formula:=Rng.Formula,
> toabsolute:=True)
> Else
> Rng.Formula = Application.ConvertFormula( _
> fromreferencestyle:=xlA1, Formula:=Rng.Formula,
> toabsolute:=True)
> End If
> Next Rng
> End Sub
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
>
> "Mike" <Mike@discussions.microsoft.com> wrote in message
> news:3CB541E1-8A7B-4BD3-B45D-D117D2270350@microsoft.com...
> > Is it possible to change a large range of very complex formulas
> > from relative
> > reference to absolute reference. Something other than the
> > replace command?
> >
> > thanks, Mike
>
>
>

JulieD, that is a very good idea. Unfortunately, there are to many different
cells involved.

Mike


"JulieD" wrote:

> Hi Mike
>
> one option that might work if the cell references you want to replace are
> used multiple times and that is to use range names, for example if you have
> the formulas
> =A1*A2
> =A1*A5
> =A1+A10
> and you now want to make A1 absolute, select A1, click in the name box (box
> to left of formula bar above column A) and type a name for the range e.g.
> Rng_A1
> and press ENTER (v. important)
> now choose
>
> insert / name / apply and click ok
>
> you will end up with
> =Rng_A1*A2
> =Rng_A1*A5
> =Rng_A1+A10
>
> and range names are absolute references.
>
> Please test this on a copy of your workbook first to see if it achieves what
> you want.
>
> Cheers
> JulieD
>
>
> "Mike" <Mike@discussions.microsoft.com> wrote in message
> news:3CB541E1-8A7B-4BD3-B45D-D117D2270350@microsoft.com...
> > Is it possible to change a large range of very complex formulas from
> > relative
> > reference to absolute reference. Something other than the replace command?
> >
> > thanks, Mike
>
>
>