covert only cell references in selected cells into value leaving the existingcell formula
Sub Convert2()
'Converts cell references to values within the
'Selected cell's formulas
'Re-Written by Bernie Deitrick Jan 17, 2005
Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim i As Integer
Dim myCell As Range
Dim mySel As Range
Const Operators As String = "=+-*/^()"
For Each mySel In Selection
strForm = mySel.Formula
strOrig = mySel.Formula
For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), "*")
Next i
Addr = Split(strForm, "*")
For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotCell
Set myCell = Range(Addr(i))
strOrig = Replace(strOrig, Addr(i), myCell.Value)
NotCell:
Resume GoOn
GoOn:
Next i
mySel.Formula = strOrig
Next mySel
End Sub
HTH,
Bernie
MS Excel MVP
"al007" wrote in message
oups.com...
Can you make it do-able for either a selection of contiguous cells or
non contiguous cells as I would like to have it in my personal macro
collection.
thxs
|