Type Mismatch error in VBA code

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

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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Hi,

Thanks to all.

Rgds

Ramana