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.

    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,"

    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!

Register Yourself Login