View code for removal of unwanted text in cells
I am getting the #DIV/0! based on some of the cells with N/A. Apparently it
views the N/A as a zero and gives #DIV/0! for the formula in those cells. Is
there a VBA that I can enter into the sheet view code for this? If not where
do I insert the formulas that you wrote?
--
Thank you!
"Jim Thomlinson" wrote:
Proper functions will clean that up for you...
check your denominator before dividing to eliminate Div by Zero
=if(A1 = 0, 0, A2/A1)
=if(countif(A1:A10, "MyValue") = 0, "Not found", vlookup("MyValue", A1:B10,
2,false))
clears up N/A errors.
The one thing I would add is try to be specific in which error you are
catching. You can use IsError but that cathces everything which is
potentially dangerous. If a cell is deleted or such leading to a #ref error
you do not want down stream formulas to ignore that error and return a value
that is not valid. Better to show the error value than the wrong value.
--
HTH...
Jim Thomlinson
"Doug" wrote:
How can I make a cell blank when it has a certain phrase such as #DIV/0! or
N/A. I have a lot of these and it would be much better without them. I
prefer a blank cell. Any ideas out there?
--
|