Share via

How to put null in Excel cell(s) with IF function

Anonymous
2013-09-03T18:12:02+00:00

I need to test for If another cell is Null then put a null in a cell with the =If(,,) function in Excel.

What I would like to do is:

Range("C3").formulaR1C1 = "=if(R[5][C-1] <> 0,RC[-1]/R[5]C[-1], Null)"

When I put this statement in VBA code, Null is not put in the cell. I do not want a blank or 0, I need to be able to know no data is available.

Does anyone know how to do this?

Thank you.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2013-09-09T17:17:13+00:00

    According to VBA HELP,

    The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string.

    Here are some interesting values, which may help your understanding a bit better

    range("a1").Clear

    ?isnull([a1])

    False

    ?[a1]=""

    True

    ?worksheetfunction.CountBlank([a1])

     1

    7 people found this answer helpful.
    0 comments

13 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-09-03T18:22:58+00:00

    Hi,

    Use the NA() worksheet function.

    Range("C3").FormulaR1C1 = "=IF(R[5]C**[**-1] <> 0,RC[-1]/R[5]C[-1], NA())"

    Cheers

    Rich

    PS. Note you had the [ in the wrong place in the first  R[5]C**[**-1]

    20+ people found this answer helpful.
    0 comments
  2. Anonymous
    2016-11-29T19:56:02+00:00

    Please up vote the inclusion of a proper NULL() function on the Excel User Voice page.   I think this function would be helpful in your situation (and many others)

    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9095062-give-us-a-proper-null-worksheet-function

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2013-09-03T20:48:56+00:00

    This does not put a null in the cell, it puts #NA.

    This could be okay. I need to import a lot of data (more than 1 Million Excel rows from several Excel sheets in multiple files) into Access records for sorting and filtering.

    I don't think Access will take the #NA in a transferSpreadSheet import without some configuring?

    So this is helpful as I would like to do this as #NA is visible and a Null is not. But I need to know how to import to Access.

    Thanks.

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2013-09-03T21:35:57+00:00

    I think you're going to have to tweak the Access side in this case.  To the best of my knowledge there really is no way to have a formula in a cell and not result in something.  Usually when that something is intended to be nothing, then it turns out to be a zero length string ( "" ) or zero.  Now if you want to hide the #N/A! you could set up conditional formatting to make the text same color as the cell background (fill) color just for neatness.

    Perhaps changing formula to put in a word, such as "Not Available" (ok, 2 words) vs Null or NA() would do.  Then you could import the data into Access and run an update query to empty out the field if those words were in it?

    1 person found this answer helpful.
    0 comments