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.

-----------------------------------------------
Sub CopyEvenEmptyCell()


''''''''''''''''''''''''''''''''''''''''''''
'Forces cell to show N/A if blank
''''''''''''''''''''''''''''''''''''''''''''


Dim isMyCellEmpty As Boolean
isMyCellEmpty = IsEmpty(Range("C3,E3,C4,E4"))


If isMyCellEmpty = True Then
Range("C3,E3,C4,E4").Value = "N/A"


End If
'
'
' This copies your ICMS Documentaion to the clipboard. :)
'


'
Range("B3:E6").Select
Selection.Copy


End Sub
--------------------------------------------

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.

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.

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.

Ops sorry for that. Here is the workbook file I'm working on. The copy button will only show N/A if all the cells are empty. If you type something in one of the cells it won't. Thanks

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,

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!
:

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!

I think the Proper case feature is the one interfering with the copy macro, it shows N/a instead of N/A in C3 where I use the Proper case code.