Hi all, In Range("B1:B5") I have formulas like see below

=A1
=A2
=A3
=A4
=A5

Is there some way or macro in excel that I should select Range
("B1:B5") and click some button or some thing and all the formulas
become like see below

=$A$1
=$A$2
=$A$3
=$A$4
=$A$5

above is just a small example to explain my question but I have
formulas in lots of rows and cloumns and I want them to appear as
shown above in second example. I am looking for some way to just
select the range in which i have the formulas and do some thing or run
some macro which fixes all the cell referces in formula. I hope i was
able to explain my question. Can please any friend help

Sub RelToAbs()
Dim rng As Range, cel As Range

' adapt to suit
Set rng = ActiveSheet.UsedRange
Set rng = Selection
Set rng = Range("B1:D10")

On Error GoTo errExit
Set rng = rng.SpecialCells(xlCellTypeFormulas, 23)
On Error Goto 0

For Each cel In rng
With cel
..Formula = Application.ConvertFormula(.Formula, xlA1, xlA1, xlAbsolute)
End With
Next
errExit:
End Sub

Regards,
Peter T

"K" wrote in message
...
Hi all, In Range("B1:B5") I have formulas like see below

=A1
=A2
=A3
=A4
=A5

Is there some way or macro in excel that I should select Range
("B1:B5") and click some button or some thing and all the formulas
become like see below

=$A$1
=$A$2
=$A$3
=$A$4
=$A$5

above is just a small example to explain my question but I have
formulas in lots of rows and cloumns and I want them to appear as
shown above in second example. I am looking for some way to just
select the range in which i have the formulas and do some thing or run
some macro which fixes all the cell referces in formula. I hope i was
able to explain my question. Can please any friend help

Hi,

Select your cells and use this macro

Sub Sonic()
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


Mike

"K" wrote:

Hi all, In Range("B1:B5") I have formulas like see below

=A1
=A2
=A3
=A4
=A5

Is there some way or macro in excel that I should select Range
("B1:B5") and click some button or some thing and all the formulas
become like see below

=$A$1
=$A$2
=$A$3
=$A$4
=$A$5

above is just a small example to explain my question but I have
formulas in lots of rows and cloumns and I want them to appear as
shown above in second example. I am looking for some way to just
select the range in which i have the formulas and do some thing or run
some macro which fixes all the cell referces in formula. I hope i was
able to explain my question. Can please any friend help