Replace "#DIV/0!" with ""

[Annika]

I am in need of some expert advice!!
I work with large sets of data, which are then compiled by
doing averages.

Occasionally I get a #DIV/0! error, when the range to be
averaged refers to a bunch of blank cells.

I need to be able to Find and Replace those with blank
cells, but using the Find and Replace commands under the
Edit menu won't do anything.
I have also written macros to try and solve this problem,
but they are also unable to correct the problem. Does
anyone know a shortcut on this one? Emptying the erroneous
cells manually is not an option, since the data sets are
so large.
Please e-mail me with any ideas or help you may be able to
offer. I am running on Excel 2000, and my windows platform
is WindowsXP Pro.

 

[Bob Phillips]

Annika,

Try some code like this

For Each cell In Selection
If cell.Text = "#DIV/0!" Then
cell.Value = ""
End If
Next

 

[cwil99]

=IF(ISERR(your formula),0,(your formula))

would return a 0 if your current formula results in a #DIV/0 or an
other error except #N/A. Otherwise, it returns the result of you
formula