Help with Macro please.

[eddy]

I have been using the macro below for well over a year ( Excel 2003). It has worked fine
The sheet has not changed but I now get the following error when running the macro:-

Run Time Error "13"
Type Mismatch

The Line:- If Range("Z" & b).Value < c Then is highlighted in Yellow

This is the macro:-

Sub MoveExpired()

Dim a, b, d As Long
Dim c

Sheets("MainSheet").Activate

' Find Last Row Number
a = Range("k:k").Find("aDespatched", Cells(Rows.Count, "K"), , , xlRows, xlPrevious, False).Row

c = Range("S2").Value

For b = a To 6 Step -1
If Range("Z" & b).Value < c Then
Rows(b).Cut
Sheets("ExpiredGuarantees").Activate
d = Range("A65536").End(xlUp).Row + 1
If d < 6 Then
d = 6
End If
Range("A" & d).Select
ActiveSheet.Paste
Sheets("MainSheet").Activate
Rows(b).EntireRow.Delete
d = d + 1
End If
Next b


End Sub

-------

Can anyone tell me why I am suddenly getting this error?

All help appreciated.

Ed

 

[VoG]

Are there error values like #N/A in column Z?

 

[eddy]

Hi Peter,

Many thanks!

Yes that was indeed the problem. There was an error in one cell in Col Z which was giving a # error. Sorry for wasting your time, I should have looked at the data in Col Z first.

Much apprecciated Ed

 

[VoG]

This should ignore errors

Sub MoveExpired()

Dim a, b, d As Long
Dim c

Sheets("MainSheet").Activate

' Find Last Row Number
a = Range("k:k").Find("aDespatched", Cells(Rows.Count, "K"), , , xlRows, xlPrevious, False).Row

c = Range("S2").Value

For b = a To 6 Step -1
    If IsNumeric(Range("Z" & b)) Then
        If Range("Z" & b).Value < c Then
        d = WorksheetFunction.Max(6, Sheets("ExpiredGuarantees").Range("A" & Rows.Count).End(xlUp).Row + 1)
        Rows(b).Cut Destination:=Sheets("ExpiredGuarantees").Range("A" & d)
        Rows(b).EntireRow.Delete
        End If
    End If
Next b
End Sub