FIND and REPLACE characters needed
 

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

FIND and REPLACE characters needed
 

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



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

FIND and REPLACE characters needed
 

A big thanks Bernie, that was excellent!!!


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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



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