Change

strOrig = Replace(strOrig, Addr(i), myCell.Value)

to

strOrig = Replace(strOrig, Addr(i), IIf(myCell.Value = "", "0",
myCell.Value))

HTH,
Bernie
MS Excel MVP




"al007" wrote in message
oups.com...
Bernie,
the revised code is working great except if a referenced cell is empty
- is it possible for you to put value = zero for any empty referenced
cell?

thxs
al


Bernie Deitrick wrote:
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