Try something like this:

Option Explicit
Public Function UnRound(theCell As Variant) As Variant
Dim strFormula As String

If IsEmpty(theCell) Then Exit Function
strFormula = theCell.Formula

If Left(strFormula, 6) = "=ROUND" Or Left(strFormula, 7) = "=-ROUND"
Then
If Left(strFormula, 2) = "=-" Then strFormula = "=" &
Right(strFormula, Len(strFormula) - 2)

strFormula = Right(strFormula, Len(strFormula) - 7)
strFormula = "=" & Left(strFormula, Len(strFormula) - 3)
UnRound = theCell.Parent.Evaluate(strFormula)
Else
UnRound = theCell
End If

End Function


Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

"hooroy63" wrote in message
...
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