changing many cells reference to absolute

W

Woody13

I have a huge spread sheet and need to change the cells from relative to
absolute reference. In other words the cell reads =A2 and I need to change
it to =$A$2. I have hundreds of cells. Anyway to not do this manually,going
into each one? Thanks
 
G

Gord Dibben

Woody

You would need VBA to make global changes to cell references.

Here are four........

Sub Absolute()
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()
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()
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()
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

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

Gord Dibben Excel MVP
 
W

Woody13

Thanks. Used the top one. It works. You just have to highlight what you
want to change to absolute and then run the macro. Thanks.
 
G

Gord Dibben

Thanks for the thanks and you're welcome.

Gord

Thanks. Used the top one. It works. You just have to highlight what you
want to change to absolute and then run the macro. Thanks.
Click to expand...
 
Top