On Wed, 30 May 2012 15:43:00 +0000, plotzmoney wrote:

Ron, thank you for the response. I was looking for what you were eluding
to in your last question. Where all formulas, even within referenced
cells, would be converted to values. Again, thanks for chiming in and
for the help.

To have this run as a macro, on the range you select, as you specified in your original post, try this macro instead:

===================================
Option Explicit
Sub ConvertFormula()
Dim re As Object, mc As Object, m As Object
Dim re2 As Object, sRepl As String
Dim s As String, v As Variant
Dim i As Long
Dim c As Range, r As Range

For Each r In Selection

s = r.Formula

Set re = CreateObject("vbscript.regexp")
Set re2 = CreateObject("vbscript.regexp")

With re
.Global = True
.Pattern = "\$?\b(?:XF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?"
End With

If re.Test(s) = True Then
Set mc = re.Execute(s)
For Each m In mc
If InStr(m, ":") 0 Then
ReDim v(1 To Range(m).Count)
For i = 1 To UBound(v)
v(i) = Range(m)(i)
Next i
sRepl = "{" & Join(v, ", ") & "}"
Else
sRepl = Range(m).Text
End If
With re2
.Global = True
.Pattern = "\b" & m & "\b"
s = .Replace(s, sRepl)
End With
Next m
End If

r.Offset(columnoffset:=1).Value = r.Text & s
Next r
End Sub
====================================