Error loop through #N/A values

[gti_jobert]

Hi all,

I have a loop that selects each cell in a column, but it comes up with
an Type Mismatch Error 13 as many of my cells contain '#N/A'. I am
using a vlookup to get these values and I know I can use an
=IF(ISNA(VLOOKUP(D2,A2:B6,2)=TRUE),"",VLOOKUP(D2,A2:B6,2)) to get rid
of these values BUT I have around 50,000 records and doing the INSA
VLOOKUP will take foreva!!

Is there a way in VBA to loop through my cell values regardless if it
contain #N/A?

TIA....any help appreciated.

 

[gti_jobert]

I have also tried copying the cell range and paste special values but
still it comes up with an error.....anyone know why?

 

[Andrew Taylor]

You can use the IsError function to check for errors:

Dim c As Range
For Each c In Selection
If IsError(c.Value) Then
Debug.Print "error in " & c.Address
End If
Next