```
Sub Main()
' David Brett 9th Dec 2003
Dim Myrange As Range
Dim Cel As Range
On Error Resume Next
' Look at formulas only, the error check prevents an error if there are no formulas in the selection
Set Myrange = Intersect(Selection, Selection.Cells.SpecialCells(xlFormulas))
On Error GoTo 0
' Leave Main sub if there are no formulas
If Myrange Is Nothing Then Exit Sub
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For Each Cel In Myrange.Cells
' Four valid arguments
' "Rel" = Relative Referenced
' "Abs" = Absolute Referenced
' "Row" = Absolute Row Referenced
' "Col" = Absolute Column Referenced
'This example converts every second cell reference of each formula in the selection to Absolute
' ReplaceRef Function = (Pattern to find formulas, Desired Reference, formula ref to be changed)
Cel.Formula = ReplaceRef(Cel.Formula, "([$]{0,1})([A-Z]{1,2})([$]{0,1})(\d{1,5})", "Abs", 2)
Next
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Function ReplaceRef(str1 As String, Patrn As String, replStr As String, FormulaRef As Integer)
Dim Reg
Dim RegEx
Dim Subm
Dim NewForm As String
Set RegEx = CreateObject("vbscript.regexp") ' Create regular expression.
RegEx.Global = True
RegEx.Pattern = Patrn ' Set pattern.
RegEx.IgnoreCase = True ' Make case insensitive.
Set Reg = RegEx.Execute(str1)
If Reg.Count < FormulaRef Then 'If the formula is not long enough then exit
ReplaceRef = str1
Exit Function
End If
'the first submatch is (0) so submatch n = Reg(n-1)
Set Subm = Reg(FormulaRef - 1).submatches
Select Case Application.WorksheetFunction.Proper(replStr)
Case "Rel"
NewForm = Subm(1) & Subm(3)
Case "Abs"
NewForm = "$" & Subm(1) & "$" & Subm(3)
Case "Col"
NewForm = "$" & Subm(1) & Subm(3)
Case "Row"
NewForm = Subm(1) & "$" & Subm(3)
Case Else
'conversion input was invalid
ReplaceRef = str1
Exit Function
End Select
ReplaceRef = Application.WorksheetFunction.Replace(str1, Reg(FormulaRef - 1).FirstIndex + 1, Len(Reg(FormulaRef - 1)), NewForm)
End Function
```