Remove unwanted values

[Guest]

I have the following code:

Worksheets("Auto_Working").Range("A2:A11").Value = ListBox2.List

ListBox2 contains 2237, 2388
It produces:

A
1 Employees
2 2237
3 2388
4 #N/A
5 #N/A
6 #N/A
7 #N/A
8 #N/A
9 #N/A
10 #N/A
11 #N/A

When it gets to the #N/A processing data, it has to be manualy stopped.
Please help, I have tried the following to remove them.

For Each Cell In Range("A2:A11")
If LCase(Cell.Value) = "#N/A" Then
Cell.Resize(1, 5).ClearContents
End If
Next Cell

This caused a error, and could not continue or find the #N/A. It stopped in
it's tracks.

 

[Guest]

I would be inclined to rewrite teh formulas to avoid the #NA's but that being
said this code should work for you...

For Each Cell In Range("A2:A11")
If UCase(Cell.Text) = "#N/A" Then
Cell.Resize(1, 5).ClearContents
End If
Next Cell

Note the Cell.Text to return the text of the cell and not the value and the
use of UCase not LCase...

 

[Guest]

How would you sugest that, that would seem cleaner, as there are 1 to 10
employees, possibly more that could be used here. How can I re-write to make
the #N/A not be there?

 

[Guest]

Depends on the formula you are using in your cells. I will assume that you
are using a Vlookup. If so then you can do a number of things. My preference
is to use a countif (count the number of matching values before you try to
match).

=if(countif($C$1:$C$10, B1)=0, "", vlookup(B1, $C$1:$C$10, 2, false))

There is also an ISNA function that you could use to determine if the
function is returning NA

=if(isna(ThisFunction), "", ThisFunction)