PDA

View Full Version : Solved: converting relative addresses to absolute



K. Georgiadis
03-25-2010, 07:49 AM
Is there a macro to convert to absolute cell references an entire column of numbers that were originally keyed in as relative addresses? The column of numbers in question draws its data from another part of the workbook and the objective is to be able to paste and use that column in yet another part of the workbook without losing the data links

Bob Phillips
03-25-2010, 07:54 AM
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

K. Georgiadis
03-25-2010, 08:05 AM
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


Just highlight (select) the range with the relative address formulas and run the macro? (which I already tried without success).

mbarron
03-25-2010, 08:54 AM
Change the
xlRelative to xlAbsolute

K. Georgiadis
03-25-2010, 09:19 AM
Change the
xlRelative to xlAbsolute
That was the answer! Thanks xld and mbarron.