Maybe:

For Each c3 In Worksheets("calculation").Range("c24:c1203")
If c3.Text <> "#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

Hope this helps
Rowan

bobby wrote:
> 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
>