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).PasteSpecia l Paste:=xlValues
Worksheets("result").Range(c3.Address).PasteSpecia l 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).PasteSpecia l Paste:=xlValues
Worksheets("result").Range(c3.Address).PasteSpecia l 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).PasteSpecia l Paste:=xlValues
Worksheets("result").Range(c3.Address).PasteSpecia l 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).PasteSpecia l Paste:=xlValues
Worksheets("result").Range(c3.Address).PasteSpecia l 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" wrote in message
oups.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).PasteSpecia l Paste:=xlValues
Worksheets("result").Range(c3.Address).PasteSpecia l 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