How can I clear cells that have focus if value is "#N/A"?

**imaquila** · 02-03-2012, 03:56 PM

SOLVED I'm using combo boxes linked to tables to fill their respective lists. If an item is on the list the Table lookups fill in most of the remaining cells automatically. If the item is NOT on the list the Table lookups return "#N/A" instead of Item Description and Price. I would like clear the values of those cell so that new values can be entered into those cells.

Your help is appreciated!

**john55** · 02-03-2012, 04:06 PM

as an option

Sub Button1_Click()
[a:c].Replace "#N/A", "", xlWhole  'change range for yr needs
End Sub

**imaquila** · 02-03-2012, 07:15 PM

In the long run, I'd prefer not to have to add a special button just to clear a few boxes if it could be avoided but as a learning experience, I gave it a try. The first thing I noticed was that unlike my combo boxes, the command button is not listed in the dropdown when going back the code for my sheet. The next thing I noticed was that I couldn't pull up a properties list for my new command button. At that point I decided to try to get creative and see if I could use it in the LostFocus routine of my combo box.

Private Sub cbxItems_LostFocus()
Range("D12").Replace Chr(34) & "#N/A" & Chr(34), "", xlWhole
End Sub

I'm not familiar with "Replace" or "xlWhole" so none of my variations returned anything but "Reference Not Valid" errors.
ie.

Range(D12).Replace Chr(34) & "#N/A" & Chr(34), "", xlWhole
D12.Replace Chr(34) & "#N/A" & Chr(34), "", xlWhole
"D12".Replace Chr(34) & "#N/A" & Chr(34), "", xlWhole
(D12).Replace Chr(34) & "#N/A" & Chr(34), "", xlWhole

**john55** · 02-04-2012, 01:37 AM

try it in worksheet event
if you have #N/A in that range it'll be replaced ...with nothing!

Private Sub Worksheet_Change(ByVal Target As Range)
[a:c].Replace "#N/A", "", xlWhole  'change range for yr needs
End Sub

**imaquila** · 02-04-2012, 02:20 PM

Thanks John! I see that it was a mistake to take the brackets to mean [insert your range here]. I was so caught up in trying to find a working syntax for a single cell at a time and it proved to be so much more elegant than that!
Thank YOU!

**john55** · 02-05-2012, 02:25 AM

Glad it helped you!
thx!

If your problem is solved, please say so clearly, and mark your thread as Solved:
Click the Edit button on your first post in the thread,
Click Go Advanced,
select [SOLVED] from the Prefix dropdown,
then click Save Changes.

**imaquila** · 02-05-2012, 12:29 PM

Thanks for the instructions, I *thought* the Star was selected to mark it as "solved".
I appreciate it! Went to Advanced and did not see anything indicating "Solved". Please let me know if there's anything more I can do to ensure that you get credit for your time and effort.