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
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:
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
Anyway, my workaround was this:
Since, in my case, if #VALUE! appears in a cell, it's because the cell above it says "Missing,"
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.)
Don’t have an account yet? Register yourself now and be a part of our community!