Replace zeros, #N/A and #DIV/0 with a text message like missing data without o

[Tanner]

I know this can be done using functions, but I was looking for some code.


It would be activate by a button which I know I to assign to a macro

It would select a range (that I will hard code into the macro) or use one of the bottom up statements

It would only run on a single worksheet

It would find all of the above errors messages and zeros (at the same time) and replace those with text like "need information", "missing information", etc.

But it would not overwrite the underlying formulaes in the cells that are now filled with the text.


I am using Excel 2010.


Thank you!


Best,


Tony

 

[imgonna]

Hi


Not sure you can do this.


Conditional formatting will let you trap and error and alter the cell format:


"=ISERROR($B$2)" for example and set the cell to FILL with RED and RED Text ....


Cheers

Glen

 

[vasim]

You want to remove 0 amd error and keep the formula, why dont you use conditional formatting instead.

 

[ananthram]

I think you can overwrite formula with Ctrl+H(selected cells) and replace them to your needful information whether it is

Need information or Missing information

 

[anupamtiwari05]

Hi Tanner,


I have not understood your requirement very well however tried something like below that can may help you. Please try and let me know.

[pre]

Sub ReplaceErrorValue()

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If IsError(cell) = True Then

cell.Value = "invalid data"

End If

Next

[/pre]

End Sub


Thanks & Regards,

Anupam