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


"Merlin" wrote in message
...
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 wit hanalphabeticcharacter.

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

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

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

Regards,
-----Original Message-----
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.
.

That worked! I should have thought of that, but I have
used iserror very little.
Thanks!!
-----Original Message-----
I suggest using the if iserror function in the lookup.
example below

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

Regards,
-----Original Message-----
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.
.

.