FIND and REPLACE characters needed

  • Thread starter Thread starter Peter C
  • Start date Start date
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
 
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
 
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
Click to expand...
 
Back
Top