absolute formula in Excel

[bhr]

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

 

[Dav]

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

 

[Harald Staff]

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

 

[Gord Dibben]

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

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