Geek asks and answers

Excel VBA find row: Error 2015

I need to execute a piece of code ...

  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 a 2015 error! The worksheet contains the text I want in the formula.

Any ideas why I get the error message?

thank

+3
source share
2 answers

As follows from the comments on Q, it Error 2015happens because your formula in the worksheet returns an error #VALUE!. You can use it with IsError:

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

You do not need to use 'Set' in your code. You use this only to assign a reference to an object. Try: -

For Each ws In Workbooks(file).Worksheets
     With ws

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

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

Hope this should work.

0
source

More articles:


All Articles