Find "#N/A" and clear

D

dolphinv4

Hi,

I'd like a macro to look into range "A1:A100" and if it
finds "#N/A", to clear the content.

but because the content in my range "A1:A100" is updated
through the =vlookup, the macro can't seem to find that
the cell is "#N/A". What should I do?

Thanks!
Val
 
G

Guest

Try this:
Dim cell as variant
sub FindItNow()
for each cell in range("A1:A100")
if not cell.value then
cell.value=""
else
end if
next
end if
end sub

Try it on a copy first before you try anything on the real
thing, just in case!

Mark E. Philpot
 
A

Arvi Laanemets

Hi

Maybe you consider error checking in your formula - something like

=IF(ISERROR(VLOOKUP(...)),"",VLOOKUP(...))
or
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))
 
Top