Error loop through #N/A values
 

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,A 2: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
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=559217

Error loop through #N/A values
 

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


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=559217

Error loop through #N/A values
 

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




gti_jobert wrote:
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,A 2: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
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=559217