Excel VBA find string : Error 2015

Question

I have to following code snippet ...

  Public Sub FindText(path As String, file As String)
    Dim Found As Range

    myText = "test("

    MacroBook = ActiveWorkbook.Name

    ' Open the File
    Workbooks.Open path & file, ReadOnly:=True, UpdateLinks:=False
    For Each ws In Workbooks(file).Worksheets
     With ws

       Set Found = .UsedRange.Find(What:=myText, LookIn:=xlFormulas, _
                      LookAt:=xlPart, MatchCase:=False)

       If Not Found Is Nothing Then
        ' do stuff
        ' ...

I see in the debugger that Found contains Error 2015! The sheet contains the text I want in the formula.

Any ideas why I'm getting the error?

Thanks

Solution

As follow up from comments to the Q, Error 2015 occurs because your formula in the sheet returns #VALUE! error. You can handle it using IsError:

If Not Found Is Nothing Then
    If Not IsError(Found) Then
       ' do sth
    End If
End If

Comments

[Rueful Rabbit]

Unfortunately the debugger complains about that. "Object variable or With block variable not set"

[user3540466]

is there a way of finding which formula is giving the error?

[AlexT82]

Actually, "Found" is a Range, and ranges you normally need to "SET"... so Ruefuel is right on needing that part.