Remove unwanted values

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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...
 
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?
 
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)
 
Back
Top