Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Converting Cell References Rel<>Abs ?

0 views
Skip to first unread message

blewyn

unread,
Nov 3, 2003, 5:34:36 AM11/3/03
to
Is it possible to select a range of cells and convert all their
references from relative to absolute, without having to type a $ in
the right places in each and every one ?

Cheers,

Blewyn

Chip Pearson

unread,
Nov 3, 2003, 6:46:00 AM11/3/03
to
Blewyn,

You can use a macro to convert the formulas.

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


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


"blewyn" <ble...@btinternet.com> wrote in message
news:1f63b835.03110...@posting.google.com...

blewyn

unread,
Nov 4, 2003, 1:20:32 AM11/4/03
to
Ta Chip !
0 new messages