How to force cells to return n/a if left blank?

  • How do I create a code that forces specific cells to show or return N/A is they are left blank or empty?


    This is the code that I created but it only works if all cells are left empty. I want them to work in such a way that if one, or two, or three or all of the cells are empty it will return/show N/A in it.



  • Hello,


    Could you explain a bit more a couple of things ...


    1. What is your ultimate objective with this macro ?


    2. How do Blank cells differ from Empty cells in your file ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for the prompt response!


    1. What is your ultimate objective with this macro ?
    The macro will copy a range of cells to the clipboard, ready to be pasted to a notepad or documentation that we use in the office.



    2. How do Blank cells differ from Empty cells in your file ?
    The cells are of course empty at first but needs to be filled up with text like name, date, id# and reference number. But if the user of the excel file leaves it blank or empty without typing anything in it, it should return N/A in it when the copy macro is run.

  • Hi again,


    Could the following be of any help ...


    Code
    Private Sub Worksheet_Deactivate()
    Dim c As Range
    Dim rng As Range
    Set rng = Range("C3:C4,E3:E4")
    For Each c In rng
        If c = "" Then c = "#N/A"
      Next c
    End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for this but I tried it but nothing is happening. I deleted the CopyEvenEmptyCell Macro and created a new Copy Macro to select the range I want to copy, but nothing is happening. N/A not showing on blanks cells.

  • Hi again,


    The macro is an event macro which is to be located in the respective sheet module ...


    As its name implies, the vent will trigger the macro ... in this case, leaving the worksheet will check your range ...


    Another event might be more suitable ... depending on your workbook structure ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I would like to use the macro, just want to edit the code in it so that it will work for all the cells in the range. What's happening is if I use the Macro code I created above, it will only show N/A if I leave all the cells blank. It should show/return N/A on the other cells even if only one of the cells is filled up. With the code I have, if I type something on C3 it will not return N/A on cells C4, E3 & E4 even if it's empty. It should return N/A on the other cells because they are empty.

  • Hi,


    When you say all the cells in the range ... are you referring to the 4 cells or to some other cells ...


    Would you mind attaching a sample workbook ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • A picture is an inert object ... with which nothing can be done ...


    A sample file would be a lot more appropriate ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sorry for the late reply, just came back from work. I tried it and it's working already. Thank you very much. I really appreciate it,


    Glad you could fix your problem ... :wink:


    Thanks ... for your thanks ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • It's been a awhile now but I just noticed that the code you've used is giving me a "Run-time error 13: Type mismatch". If I just leave the fields blank and hit the Copy button, the N/A will show up on the blank fields which is good, but the 1st dropdown box automatically activates/opens. then when I select anything and hit Copy again it will give the Run-time error 13: Type mismatch. Tried also other instances and also showing the same error. Be trying to debug but not really good at this. Will try further, I hope you can still assist me on this. Thanks in advance! :)

  • Hello, I was able to debug the code by removing the mismatch. I just remove the # in "#N/A" and made it into just "N/A".


    But I just noticed a minor issue. The first field (Cell C3) shows "N/a" with a small "a" instead of the "N/A" like the other fields.


    And also is there a way to tab in and out of the Dropdown combo boxes and be able to type in it with auto complete as it was designed. Right now if I'm in the field "E4", I'm able to tab to the dropdown combo box if I edit it but if I just skip without typing anything in it, it won't work. I'm also unable to tab to the 2nd combo box from the 1st combo box. Thanks! :)
    :

  • Hello,


    In my humble opinion, you should try to fix your different problems ... one after the other ...


    Regarding the initial question ... you can test following macro ...



    Regarding all the other issues ... you have designed many Event macros which might interfere ... :wink:


    Ideally ... you should describe the way you are using your worksheet and list all the features you would like to happen automatically ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Yes as I have said, I already removed the # in the "#N/A" and just used the "N/A" instead just like the new code you provided, it's already working thanks. But it shows N/a in cell C3 instead of N/A. I set C3 to force text to Proper case, I think that's the reason why it shows N/a instead of N/A when I hit the Copy button. Is there any way to bypass or disregard the Proper case feature of the cell if the Copy button is hit/click?


    ICMS Documentation Basic Descriptions:

    • Caller's Name: For caller's name
    • AMI#: Patient's ID
    • Contact#: Phone number
    • Auth/Case#: Reference #
    • Call Reason: Reason for the call
    • Call Resolution: Resolution given
    • Call Resolution Preview Window: To show call resolution selected.


    Features:

    • Caller's Name: Proper case
    • AMI#: Upper case
    • Contact#: Auto Phone number format
    • Auth/Case#: Upper case, be able to tab to combobox1
    • Call Reason: Dropdown combobox1, autocomplete, be able to tab to combobox1 or back to previous field
    • Call Resolution: Dropdown combobox2, autocomplete, should be dependent on combobox1, be able to tab to Copy button or back to combobox1,
    • Call Resolution Preview Window: I just used label to show the entire length of the Call Resolution selection, as I'm unable to do a Text Wrap property for combobox. A text wrap property for combobox should be good to see the entire lenght of the text string. Or if there is a way for us to edit directly from the label window if we have to edit what is selected or if we have to manually enter own call resolution if it's not on the list.
    • Copy: Copies the documentation and shows N/A if field is left empty.
    • Clear: Clears the template for next caller, select C3 for next entry.


    Some of the features listed here is still under works. I'm still working on the tab features, Auto complete & Dependent combo box features which would make documentation a lot easier for us. Thanks!

  • Hello,


    Based on you tests ...


    Is the only interference between all your macros ... related to "the Proper case feature of the cell if the Copy button is hit/click" ... ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

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