A unknow find error...

I'm running a bit of code to try and find cells that have "Complete" in them in a certain range. In an effort to avoid errors I use an If statement that is [i]supposed[i] go to an exit do if there is no cell with "Complete" in the range. Here's the thing: Whenever I run this bit of code Excel chokes if the if statement is NOT true, I get an error instead of going to the "Else" which is "Exit Do". Can anyone tell me what I'm doing wrong?

The If statement is as follows (and I need to leave the Range statement “As is” as normally the rows are Variables):

If Not Worksheets("Joy 2006").Range(Cells(7, 7), Cells(57, 7)).Find("Complete") Is Nothing Then

Originally Posted by davesexcel

lets see more of the code

Well while this is failing in a much longer macro, it is also failing in the following "test" macro.

Sub test()
Dim intRow As Integer


If Not Worksheets("Joy 2006").Range(Cells(7, 7), Cells(57, 7)).Find("Complete") Is Nothing Then
intRow = Worksheets("Joy 2006").Range(Cells(7, 7), Cells(57, 7)).Find("Complete").Row
End If
MsgBox (intRow)

Worksheets("Joy 2006").Range(Cells(7, 7), Cells(41, 7)).Select
End Sub

Ok I've sorted this out. The problem only occured when the active sheet was not "Joy 2006" so I changed the code to the following and now it works:

Sub test()
Dim intRow As Integer


If Not Worksheets("Joy 2006").Range(Worksheets("Joy 2006").Cells(7, 7), Worksheets("Joy 2006").Cells(55, 7)).Find("Complete") Is Nothing Then
intRow = Worksheets("Joy 2006").Range(Worksheets("Joy 2006").Cells(7, 7), Worksheets("Joy 2006").Cells(57, 7)).Find("Complete").Row
End If
MsgBox (intRow)

End Sub