#div/0!

[Tamesh]

With the data I have, there isn't always going to be a
value to divide by hence the #div/0! error. If I use the
if(iserror(),"",()) method, I'd have to re-input the
formula for every cell that does calculations. The data
is collected from a workbook that is multipaged with
named tabs thus making it a bit tedious to use the
formula in every cell (long tab names). Is there a way
to use a macro or something of that nature to fix this
issue on a large scale rather than one by one?

 

[Cecilkumara Fernando]

Tamesh,
if #div/0! error is in a cell with formulas use
Range("A1").Select
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
Selection.ClearContents

if you have done copy and paste special values then
Range("A1").Select
Selection.SpecialCells(xlCellTypeConstants, 16).Select
Selection.ClearContents
HTH
Cecil

 

[Tamesh]

Cecil, exactly what does the code you just gave me do?
Thx for the help.

 

[Robert Black]

Have you tried Search & Replace for the text of the formula?

 

[Tamesh]

I tried that but I kept getting the message that my
search string could not be found (original value of cell)

 

[Cecilkumara Fernando]

Tamesh,
The code I gave you will clear the content of the cell if the formula in
that cell generates an error but I think you want to have the formula in
tact,
this code when run will change the formulas in the selection from
=The formula to
=if(iserror(The formula),"",The formula)

Sub Addiserror()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = "=if(iserror(" & _
Replace(cell.Formula, "=", "", 1, 1, vbTextCompare) & _
"),""""," & _
Replace(cell.Formula, "=", "", 1, 1, vbTextCompare) & ")"
End If
Next cell
End Sub

HTH
Cecil