# Replace #VALUE! error with text

• I have a row that has cells with either #VALUE! or #DIV/0! in them.

I've been trying to replace the #VALUE! with the following text: No data

I want to keep the #DIV/0! cells as-is.

Any suggestions?

Thanks

• Hi Tested
If a cell shows #VALUE it is because it cannot evaluate what you asked for.
eg If A1 has a value of 5, and A2 has "No", then =SUM(A1:A5) will give #VALUE because it cannot add a number and text together.

So you can get around this by querying if there is an error: =IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))
Thus if there is an error, the formula returns "No Data", otherwise you get the sum.

Similarly, for #DIV/0!, you can (should?) test to see if the denominator is 0. If it is then put "", else the result of the division.

Hope this helps

KiwiSteve

• Thanks, KiwiSteve,
By using this code, I think it picks up both #VALUE! and #DIV/0! because it considers both to be errors. Unless I'm doing something wrong.

• #Value! errror type is equal to 3.

Therefore, this formula should work.

=IF(ERROR.TYPE(A1) = 3,"No Data",A1)

Cheers

BOB. :guitar:

B_O_B

• Thanks, BeginnerBob,
I've tried and tried to make your code work, but I think due to my current inexperience, I'll need to revisit once I learn more. I did find this great list on Ozgrid elsewhere that I could not find before through many previous searches. It's this:
#NULL! = 1
#DIV/0! = 2
#VALUE! = 3
#REF! = 4
#NAME? = 5
#NUM! = 6
#N/A = 7
Anything else#N/A

Since, in my case, if #VALUE! appears in a cell, it's because the cell above it says "Missing,"

Code
``````Dim cell As Range
Range("F7:O7").Select
For Each cell In Selection
If cell.Value = "Missing" Then
'replace value of next cell down with statement
cell.Offset(1, 0).Formula = "No data"
End If
Next``````

It works, anyway. (If anyone would like to show me BeginnerBob's code in the context of a whole submodule, that would be great.)

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!