Type Mismatch Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ayo - 17 Sep 2007 15:26 GMT

I need help dealing with an error message on this line of code: "If c.Value =
"#N/A" Then". I keep getting a type mismatch error and I don't know why. I am
try to tell the programme to ignore any cell that contains "#N/A" and go on
to the next cell.
I need help here. I have been working on this macro now for abot 3 weeks.

Set myRange = Worksheets(2).Range("A3:A" & lastrow)
For Each c In myRange
If c.Value = "#N/A" Then
ElseIf c.Value <> "#N/A" Then
Worksheets("Finance Data Report").Cells(rw, 2) = c.Value
Worksheets("Finance Data Report").Cells(rw, 3) = c.Offset(0, 1)
Worksheets("Finance Data Report").Cells(rw, 4) = c.Offset(0, 2)
Worksheets("Finance Data Report").Cells(rw, 5) = c.Offset(0, 4)
Worksheets("Finance Data Report").Cells(rw, 6) = c.Offset(0, 5)
Worksheets("Finance Data Report").Cells(rw, 10) = c.Offset(0, 7)
Worksheets("Finance Data Report").Cells(rw, 11) = c.Offset(0, 10)
End If
rw = rw + 1
Next c

Reply to this Message

papou - 17 Sep 2007 15:31 GMT

Hello Ayo
If Not IsError(c.Value) Then

HTH
Cordially
Pascal

>I need help dealing with an error message on this line of code: "If c.Value
>=
[quoted text clipped - 21 lines]
> rw = rw + 1
> Next c

Reply to this Message

Ayo - 17 Sep 2007 16:00 GMT

Now I am getting a "Subscript out of range" error on the row with the arrow

Set myRange = Worksheets(2).Range("A3:A" & lastrow)
For Each c In myRange
If Not IsError(c.Value) Then
--> Worksheets("Finance Data Report").Cells(rw, 2) = c.Value
Worksheets("Finance Data Report").Cells(rw, 3) = c.Offset(0, 1)
Worksheets("Finance Data Report").Cells(rw, 4) = c.Offset(0, 2)
Worksheets("Finance Data Report").Cells(rw, 5) = c.Offset(0, 4)
Worksheets("Finance Data Report").Cells(rw, 6) = c.Offset(0, 5)
Worksheets("Finance Data Report").Cells(rw, 10) = c.Offset(0, 7)
Worksheets("Finance Data Report").Cells(rw, 11) = c.Offset(0, 10)
End If
rw = rw + 1
Next c

> Hello Ayo
> If Not IsError(c.Value) Then
[quoted text clipped - 28 lines]
> > rw = rw + 1
> > Next c

Reply to this Message

Gary Keramidas - 17 Sep 2007 16:02 GMT

did you set rw to an initial value somewhere?

Signature

Gary

> Now I am getting a "Subscript out of range" error on the row with the arrow
>
[quoted text clipped - 44 lines]
>> > rw = rw + 1
>> > Next c

Reply to this Message

Ayo - 17 Sep 2007 16:10 GMT

it is set as
rw = Worksheets(1).Range("A65536").End(xlUp).Row + 1

> did you set rw to an initial value somewhere?
>
[quoted text clipped - 46 lines]
> >> > rw = rw + 1
> >> > Next c

Reply to this Message

Dave Peterson - 17 Sep 2007 17:05 GMT

Check the spelling of that worksheet "Finance data report".

My bet is that the name in the code doesn't match the name of the
worksheet--maybe extra space characters???

ps.

Another way to check for that specific error:

if c.text = "#N/A" then
'skip it
else
worksheets(....

> Now I am getting a "Subscript out of range" error on the row with the arrow
>
[quoted text clipped - 44 lines]
> > > rw = rw + 1
> > > Next c

Signature

Dave Peterson

Reply to this Message

Ayo - 17 Sep 2007 17:20 GMT

Thanks. That was it. I saw it and fixed it already.

> Check the spelling of that worksheet "Finance data report".
>
[quoted text clipped - 58 lines]
> > > > rw = rw + 1
> > > > Next c

Reply to this Message