$ sign in Formula with cell referce
 

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

$ sign in Formula with cell referce
 

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

$ sign in Formula with cell referce
 

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