FIND and REPLACE characters needed

[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

[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



"Peter C" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Peter C]

A big thanks Bernie, that was excellent!!!


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>