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?


  • 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


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

    Dim cell As Range
            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

    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!