FIND and REPLACE characters needed

P

Peter C

What characters (~, #, ?) do I use to Find and Replace item cells of first
following row with second row below?


FIND these types, eg: =(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)

Replace with these types, eg:
=IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0)

Thanks.
Peter C
 
B

Bernie Deitrick

Peter,

In general, first find and replace
=
with
'=

Then F&R
=DSUM
with
=IF(ISERROR(DSUM

Then F&R
)
with
)),0)

Then F&R
'=
with
=

Of course, =IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0)
isn't a good formula, so that isn't what you really want to do.

Better would be a macro: Select all your cells with formulas and run this
macro:

Sub MakeIfIserror()
Dim myCell As Range
Dim myForm As String

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myForm = Mid(myCell.Formula, 2, Len(myCell.Formula))
myCell.Formula = "=IF(ISERROR(" & myForm & "),0," & myForm & ")"
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP
 
P

Peter C

A big thanks Bernie, that was excellent!!!


Bernie Deitrick said:
Peter,

In general, first find and replace
=
with
'=

Then F&R
=DSUM
with
=IF(ISERROR(DSUM

Then F&R
)
with
)),0)

Then F&R
'=
with
=

Of course, =IF(ISERROR(DSUM(DIVOH,$CX$1,$DG$2:$DG$20)),0)
isn't a good formula, so that isn't what you really want to do.

Better would be a macro: Select all your cells with formulas and run this
macro:

Sub MakeIfIserror()
Dim myCell As Range
Dim myForm As String

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myForm = Mid(myCell.Formula, 2, Len(myCell.Formula))
myCell.Formula = "=IF(ISERROR(" & myForm & "),0," & myForm & ")"
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DSUM returns #VALUE! 1
Complex function needed? 5
Find and Replace 1
Find/Replace - from old to new 1
Frustrated 6
Add Cells error 2
find and replace using wildcard characters 2
Replace characters. 5

Top