.Find gets 'Error 13'

[TheAntiGates]

The attached blows up on a seemingly innocuous worksheets(i).UsedRange.Find.
It's yet another case of the ineptly programmed 'Error 13 Type mismatch' where you are told almost nothing in the error message. (Way to go, Excel authors.)

Sometimes, reissuing the .Find except changing
After:=ActiveCell.SpecialCells(xlLastCell)
to
After:=ActiveCell
goes cleanly, yet sometimes it bombs either way. Running the attached on XL03 shows both cases.

Just run the macro, or step it - it's extremely short and very quickly shows the problem in 2 flavors.
The first time, the xlLastCell code errors, but then After:=ActiveCell works.
The second time, the xlLastCell code errors, and then After:=ActiveCell also errors.

I'm completely out of ideas here. :help:banghead:

[TheAntiGates]

Would someone even confirm reproducing the behavior? I pared and pared to make the example file short and tiny and quick to use.

[Simon Lloyd]

Its because you need error handling for the last find, you need to think about what if the sheet is empty or it doesn't find a match!

[TheAntiGates]

Its because you need error handling for the last find, you need to think about what if the sheet is empty or it doesn't find a match!Really? So "type mismatch" (or any error) is the correct behavior when no match is found by a Find?

I'm happy to establish error handling for the error handling clause, but I'm trying to establish why there's an error at all.

[david000]

Try stepping through the code with the Locals window open and you'll see the second portion of the code is different in the respects that 'c' is not being set properly if it is indeed NOTHING as Simon says...



MsgBox "process the match" & " " & c.Address 'In the top part add this

'--------------------------------------------------------------------------
'second half changes
'Exit Sub 'Comment this line out!
With wksht.UsedRange
Set c = .Find(what:=sSearchText, _
LookIn:=lLookIn, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
After:=ActiveCell, _
MatchCase:=False)
If Not c Is Nothing Then 'Add this line here
err1SearchWorkbookCommonCode: 'Move these two lines here
MsgBox ".Find problem again - maybe last used row is merged. If ANOTHER error during this error handling, maybe merged row 1??"
MsgBox c.Address
End If
End With
Resume Next
End Sub