covert only cell references in selected cells into value leaving the existingcell formula
BIG THXS
Bernie Deitrick wrote:
Select the cells with the formula, and run the macro below. It will change most cell references to
values, with the exception of multi-cell ranges. That would be do-able, but would require a
re-write.
HTH,
Bernie
MS Excel MVP
Sub Convert()
'Converts cell references to values within the
'Activecell's formula
'Written by Bernie Deitrick Dec 15, 2005
Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim i As Integer
Dim myCell As Range
Const Operators As String = "=+-*/^()"
strForm = ActiveCell.Formula
strOrig = ActiveCell.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
ActiveCell.Formula = strOrig
End Sub
"al007" wrote in message
oups.com...
How can I covert only cell references in selected cells into value
leaving the existingcell formula.e.g
If A1=10 & A2=20 D1=30 & D2=40
A3= A1+A2
D3= D1+D2
I would like to have this
A3 = 10+20
D3=30+40
Thxs
|