Type Mismatch error in VBA code

[bobby]

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

 

[Rowan Drummond]

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

 

[Ken Puls]

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]

Hi,

Thanks to all.

Rgds

Ramana