Hi there,

Try using the iserror function:

For Each c3 In Worksheets("calculation").Range("c24:c1203")
If iserror(c3.Text) Then
c3.Copy
Worksheets("result").Range(c3.Address).PasteSpecial Paste:=xlValues
Worksheets("result").Range(c3.Address).PasteSpecial Paste:=xlFormats
End If
Next c3

Caveat, though... this doesn't only pick up the #N/A error. It will trigger
on the Div/0, Name and any others as well.

Ken Puls
www.officearticles.com

"bobby" <ramana_excel@yahoo.com> wrote in message
news:1131602948.493838.277430@g47g2000cwa.googlegroups.com...
> Hi,
>
> I'm trying to write VBA code to copy and paste special values. I'm
> using lookup formula in sheet1 only the values to be copied to be
> copied to sheet3 In sheet1 if the lookup condition is satisfied it
> gives the value otherwise it displays #N/A.
> My syntax is like this
>
> For Each c3 In Worksheets("calculation").Range("c24:c1203")
> If c3.Value <> #N/A Then
> c3.Copy
> Worksheets("result").Range(c3.Address).PasteSpecial Paste:=xlValues
> Worksheets("result").Range(c3.Address).PasteSpecial Paste:=xlFormats
> End If
> Next c3
>
> But while executing it is giving me the error Type mismatch and
> stopping the code. Is there any suggestion pl. Tell me.
>
> Thanks and Regards
>
> Ramana
>