Find "#N/A" and clear

[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

 

[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

 

[Guest]

I am from the MS Excel support department at http://www.enterpriseprojectconsulting.com and we would be happy to assist you in resolving your issue

Thank you

Enterprise Project Consultin

 

[Arvi Laanemets]

Hi

Maybe you consider error checking in your formula - something like

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