Hello all

we need to use absolute formula in most cells, the way that we know is that
use the normal formula and then add $ sign before cell ref.

It is a nightmare to correct all cells manually. is there any way to make it
fix?

Thanks

The reason for absolute and relative formulas and bits of formulas is
that when you copy them they adjust as you want them to. So at the
moment if your cells are giving the correct answer you need to do
nothing, if you wish to copy them then you only need to adjust one cell
accordingly for each formula type and copy it. Or am I missing
something?

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=564580

Hi

Select the cells and run this macro:

Sub ToAbs()
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula Then
Cel.Formula = _
Application.ConvertFormula(Cel.Formula, _
Application.ReferenceStyle, _
Application.ReferenceStyle, True)
End If
Next
End Sub

Note that it works only in A1 style, column headers must be letters.

HTH. Best wishes Harald

"bhr" <(E-Mail Removed)> skrev i melding
news:(E-Mail Removed)...
> Hello all
>
> we need to use absolute formula in most cells, the way that we know is
that
> use the normal formula and then add $ sign before cell ref.
>
> It is a nightmare to correct all cells manually. is there any way to make
it
> fix?
>
> Thanks

Will a macro solution be OK with you?

Try these. Ignores cells without formulas.

Sub Absolute()
'$A$1
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


Sub AbsoluteRow()
'A$1
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub


Sub AbsoluteCol()
'$A1
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub


Sub Relative()
'A1
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Tue, 25 Jul 2006 01:30:18 -0500, "bhr"<(E-Mail Removed)> wrote:

>Hello all
>
>we need to use absolute formula in most cells, the way that we know is that
>use the normal formula and then add $ sign before cell ref.
>
>It is a nightmare to correct all cells manually. is there any way to make it
>fix?
>
>Thanks

Gord Dibben MS Excel MVP