Hi All -

I'd appreciate help in writing a function that strips a ROUND "wrapper" from
a formula in another cell. For example, assume cell A3 has a formula such as
=-ROUND(SUM(sales),0) that displays 66.55. I want the put a function in,
say, B3 that is =UnRoundCell(A3), which should display in B3 the unrounded
sum of the range named Sales. Below is a simulated sheet with several other
troublesome examples. The problem is that I don't know how to get the
function to return a formula to the calling cell. In every case it returns a
"dead" text data type in column B rather than an active formula that
produces the proper result.

A B
Comments:

1 =ROUND(66.55,0) =66.55
text -- s/b a number
2 =-ROUND($E$8,0) =-$E$8
text -- s/b a formula that returns

the contents of cell E8
3 =-ROUND(SUM(Sales),0) =-SUM(Sales)
text -- s/b a formula that returns

a number (sum of Sales range)
4 {=ROUND(SUM($B$6:$B$7),0)} =SUM($B$6:$B$7) array
formula in A, but B is plain

non-array text -- s/b an array

formula that returns a sum

Below is the code I've written so far. What have I done wrong? TIA for your
help.

Function UnRoundCell(Cell) As Variant

Dim CellContents As Variant, neg As Boolean
Application.Volatile
CellContents = Cell.Formula

'if formula in cell isn't wrapped by a rounding formula,
' then use existing cell contents unchanged
If Not Left(CellContents, 6) = "=ROUND" And Not Left(CellContents, 7) =
"=-ROUND" Then
UnRoundCell = CellContents
Exit Function
End If

'determine negativity
neg = False
If Left(CellContents, 1) = "-" Or Left(CellContents, 2) = "=-" Then neg
= True
'peel rounding prefix and suffix

If CellContents Like "=-*" Then
CellContents = Right(CellContents, Len(CellContents) - 2 -
Len("Round("))
ElseIf CellContents Like "=*" Then
CellContents = Right(CellContents, Len(CellContents) - 1 -
Len("Round("))
Else
CellContents = Right(CellContents, Len(CellContents) - 0 -
Len("Round("))
End If
CellContents = Left(CellContents, Len(CellContents) - 3)

'add leading sign
If neg Then
CellContents = "=-" & CellContents
Else
CellContents = "=" & CellContents
End If

UnRoundCell = CellContents

End Function