Deleting #N/A

  • Thread starter Thread starter Merlin
  • Start date Start date
M

Merlin

I am trying to delete the #N/As leftover after converting
a failed lookup function to a value. When the loop lands
on a cell that is #N/A when I perform the following:

Do Until ActiveCell = "Done"
If ActiveCell = "#N/A" Then
Selection.ClearContents
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

I get a type mismatch error. Help says that "Variable
names must begin with an alphabetic character".

Do I need to change the variable type, or how do I do fix
this? Any help would be appreciated.
 
Hi Merlin

try
Do Until ActiveCell.Text = "Done"
If ActiveCell.Text = "#N/A" Then
ActiveCell.ClearContents
End If
ActiveCell.Offset(1, 0).Select
Loop

Cheers
JulieD
 
I suggest using the if iserror function in the lookup.
example below

=IF(ISERROR(VLOOKUP(#REF!,CSR,2,FALSE))=TRUE,0,VLOOKUP
(#REF!,CSR,2,FALSE))

Regards,
 
That worked! I should have thought of that, but I have
used iserror very little.
Thanks!!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Time Error "13": Type mismatch 6
Can Vba workout if a selection is part of a group? 0
Loop Repeats - PLS HELP 2
Evaluate Range of Cell 2
Entering data from a userform into multiple sheets 2
Issues with UserForm Box 5
Inserting 2 columns when cells don't match 6
Limit .find to one pass 8

Back
Top