Find #DIV/0! Error and Replace

[Flamikey]

I have a large workbook with many #DIV/0! errors. I do not want t
create any new formulas to recalc these cells (such as IF(ISERROR) t
resolve the error. I simply want to select the entire sheet, Fin
#DIV/0! and Replace with 0 or Blank or anything else. I just want t
get rid of the error using REPLACE.

The problem is that when I am able to find the cells with #DIV/0!bu
when I try to replace I get the message that Excel can not fin
matching data to replace. I don't get it. Excel finds it, but when
try to replace it Excel all of a sudden can't find it. I am usin
Excel 2000

 

[Dave Peterson]

Are those the only errors you have on the worksheet--no #n/a's, #ref's???

If yes, then you could go a different route:

Ctrl-A to select all the cells.
Edit|goto|special
check formulas and uncheck numbers, text, logicals (leave errors)
hit ok

if you want to clear them, hit the delete key.

If you want 0's, hit 0 and hit ctrl-enter to fill the selected cells.

If you have values (not formulas), do it again, but check Constants and Errors.

(but with values, the edit|replace would have worked, too.)

 

[Flamikey]

Thanks Dave.

I had to do a find on the other errors before using your fix, just to
be sure I wasn't going to blow away something I shouldnt. This worked
great. I am curious as to the theory of why Excel can find the errors
but can not replace them. I was using the Find/Replace command from
the edit menu, not the FIND and REPLACE functions. Thx

 

[Flamikey]

Does anybody know why the FIND/REPLACE command on the EDIT menu will
find errors but will not replace them?

 

[Peo Sjoblom]

Because it is from a formula and the formula itself does not contain the
error
That is easily fixed, you can replace it by using GoTo (F5), special and
formula errors,
then type what you want to replace with and press Ctrl + Enter

 

[Flamikey]

I still don't understand.

If I have a formula and the result is "5", I can do a FIND 5 and
REPLACE with whatever I want. (By finding values instead of formulas)

If I have a formula and the result is #DIV/0!, I can FIND #DIV/0! just
like the example above, however when I try to REPLACE, #DIV/0! can no
longer be found.

 

[Harlan Grove]

I still don't understand.

This is Excel you're discussing. Understanding is irrelevant.

If I have a formula and the result is "5", I can do a FIND 5 and
REPLACE with whatever I want. (By finding values instead of formulas)

Not, at least, in XL97. If I have =2+3 as the formula in cell A1, I can't
replace 5 with 6. This is possible in more recent versions?

If I have a formula and the result is #DIV/0!, I can FIND #DIV/0! just
like the example above, however when I try to REPLACE, #DIV/0! can no
longer be found.

In XL97, at least, the Find dialog has a 'Look in' option that the Replace
dialog lacks. If that's also the case in your version, then the problem is that
Find works on both formulas and values (and comments), but Replace only works on
formulas.