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?
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?
I don't know of a simple VBA method, but give this code version a try.
[vba]
Revised code posted below[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanks mdmackillop, I'll give it a try.
I notice it's not quite there, missing the $ in such as =%H3. Can you fix this yourself?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Revised code
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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?
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
[vba]
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
[/vba]
I tried xld's code first because it is so compact and it worked perfectly. Thanks guys!!
Yeah!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
If I knew this would upset you, I would have been more diplomatic
I can take it. (see http://www.vbaexpress.com/forum/showthread.php?t=9666)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
