Find and Replace ROUNDUP(stuff, 0)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

emkramer@gmail.com - 25 Oct 2006 19:49 GMT

I have several hundred cells with =ROUNDUP(formulas, 0). I want to
erase the ROUNDUP( ,0), but don't know how to use find and replace on
this. I can't do it in two steps, because then Excel doesn't like the
resulting formula after deleting either ROUNDUP( or ,0). Is there a
simple way to turn off formulas? Or another way around this problem?

Earl

Reply to this Message

Dave F - 25 Oct 2006 19:56 GMT

Copy the range, paste special, values.

Dave

Signature

Brevity is the soul of wit.

> I have several hundred cells with =ROUNDUP(formulas, 0). I want to
> erase the ROUNDUP( ,0), but don't know how to use find and replace on
[quoted text clipped - 3 lines]
>
> Earl

Reply to this Message

emkramer@gmail.com - 25 Oct 2006 20:14 GMT

Unfortunately, I need to keep the formulas inside of the ROUNDUP
function. I just need to delete "ROUNDUP(" and ",0)".

Thanks,

Earl

> Copy the range, paste special, values.
>
[quoted text clipped - 10 lines]
>
> > Earl

Reply to this Message

Dave F - 25 Oct 2006 21:00 GMT

Sorry I misunderstood your question.

One possible way:

1) Create a UDF, getformulaI which returns the formula in a cell.
2) Format the returned formulas as text
3) Run the MID function to extract from the text string the formula you want
4) Convert the text string to a formula.

Code for the UDF is:

Function GetFormulaI(Cell As Range) As String
'Application.Volatile = True
If VarType(Cell) = 8 And Not Cell.HasFormula Then
GetFormulaI = "'" & Cell.Formula
Else
GetFormulaI = Cell.Formula
End If
If Cell.HasArray Then _
GetFormulaI = "{" & Cell.Formula & "}"
End Function

This is rather awkward tho. There may be a different way

Signature

Brevity is the soul of wit.

> Unfortunately, I need to keep the formulas inside of the ROUNDUP
> function. I just need to delete "ROUNDUP(" and ",0)".
[quoted text clipped - 17 lines]
> >
> > > Earl

Reply to this Message