Solved: Convert a range from relative to absolute cell reference

[K. Georgiadis]

Is there a macro to convert a range of cells (say F4 to F144), linked to other cells in the workbook, from a relative to an absolute cell reference?

[mdmackillop]

I don't know of a simple VBA method, but give this code version a try.


Revised code posted below

[K. Georgiadis]

Thanks mdmackillop, I'll give it a try.

[mdmackillop]

I notice it's not quite there, missing the $ in such as =%H3. Can you fix this yourself?

[mdmackillop]

Revised code

Sub ConvertAbsolute()
Dim F As String
Dim Pos1 As Long, Pos2 As Long
Dim Num As Long, Rw As Long, Cm As Long
Dim Cel As Range
Dim Tmp As Long
For Each Cel In Selection
F = Cel.FormulaR1C1
F = Replace(F, "RC", "R[0]C")
Debug.Print F
Rw = Cel.Row
Cm = Cel.Column
Pos2 = 1
Do
Ln = Len(F)
Pos1 = InStr(Pos2, F, "C[")
If Pos1 = 0 Then GoTo DoRows
Pos2 = InStr(Pos1, F, "]")
Num = Mid(F, Pos1 + 2, Pos2 - Pos1 - 2)
F = Left(F, Pos1 + 1) & CStr(Num + Cm) & Right(F, Ln - Pos2 + 1)
Loop
DoRows:
Pos2 = 1
Do
Ln = Len(F)
Pos1 = InStr(Pos2, F, "R[")
If Pos1 = 0 Then GoTo Exits
Pos2 = InStr(Pos1, F, "]")
Num = Mid(F, Pos1 + 2, Pos2 - Pos1 - 2)
F = Left(F, Pos1 + 1) & CStr(Num + Rw) & Right(F, Ln - Pos2 + 1)
Loop
Exits:
F = Replace(F, "[", "")
F = Replace(F, "]", "")
F = Replace(F, "RC", "R1C")
Cel = F
Debug.Print F
Next
End Sub

[K. Georgiadis]

Thanks again! Am I supposed to run the macro after I highlight the range that I want to convert from relative to absolute cell reference?

[mdmackillop]

That's the way it's coded, but easily changed to loop through any defined range or row/column headed by the active cell. If you need help, let us know.

[Bob Phillips]

Sub xxConvertAbsolute()
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 cell

End Sub

[K. Georgiadis]

I tried xld's code first because it is so compact and it worked perfectly. Thanks guys!!:beerchug:

[mdmackillop]

Yeah! :banghead:

[K. Georgiadis]

If I knew this would upset you, I would have been more diplomatic :yes

[mdmackillop]

I can take it. (see http://www.vbaexpress.com/forum/showthread.php?t=9666)