Multiple cells to absolute reference at once

[PP3321]

I need to convert the whole range to absolute reference.

=A1
=A2
=A3

▼

=$A$1
=$A$2
=$A$3

I do not want to do this 1 by 1.
There are about 50 rows in my range...

What is the best way to do this...?

 

[bosco_yip]

Using Find/Replace as in :

▼



Regards

 

[PP3321]

Thank you!

 

[xlstime]

Unfortunately there is no way thru excel formula to convert relative to absolute range (apart from find and replace )

But Yes, this is possible thru VBA.

Please read below
http://www.ozgrid.com/forum/showthread.php?t=86661

code from above website

Sub CycleAbsRel()
    Dim inRange As Range, oneCell As Range
    Static absRelMode As Long
    absRelMode = (absRelMode Mod 4) + 1
     
    Set inRange = Cells.SpecialCells(xlCellTypeFormulas)
     
    For Each oneCell In inRange
        With oneCell
            .FormulaR1C1 = Application.ConvertFormula(.FormulaR1C1, xlR1C1, xlR1C1, absRelMode, oneCell)
        End With
    Next
End Sub

Thank you!

 

[PP3321]

Thank you for sharing the VBA code...