excel 2010 vba find what returns error when no records found

July 14, 2013 at 13:48:01
Specs: Windows 7
Sub Macro4()
Dim found As Boolean
On Error GoTo Err
Application.Goto Reference:="SecurityTransfer"
Range("SecurityTransfer").Select

found = Selection.Find(What:="Ishares S&P/TSX CDN Pref ETF", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
On Error Resume Next

Err:
MsgBox Err.description

End Sub

error message 91 Variable not set


See More: excel 2010 vba find what returns error when no records found

Report •

#1
August 14, 2013 at 08:09:11
Without knowing what you are trying to achieve with this code i have only modified it so that it does not crash rather throws a message box

Note: the removal of goto err and the addition of resume next

Sub Macro5()
Dim found As Boolean

On Error Resume Next

Application.Goto Reference:="SecurityTransfer"
Range("SecurityTransfer").Select

found = Selection.Find(What:="Ishares S&P/TSX CDN Pref ETF", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
On Error Resume Next

If found <> Empty Then
    MsgBox "true"
Else
    MsgBox "false"
End If
End Sub


Report •

#2
August 14, 2013 at 10:01:23
Another option, accomplished without the inefficiencies of selecting a Range.

In addtion, using Set allows you capture all of the info about the cell that the search string was found in.

Found.Row
Found.Column
Found.Interior.ColorIndex
Etc.

Sub FindSecurity()
 With Range("SecurityTransfer")
   Set found = .Find(What:="Ishares S&P/TSX CDN Pref ETF")
    If Not found Is Nothing Then
       'Do what you want if searchString is found
         MsgBox "Ishares S&P/TSX CDN Pref ETF Found in Row " _
                  & found.Row
    Else
       'Do what you want if searchString is *not* found
        MsgBox "Ishares S&P/TSX CDN Pref ETF Not Found!"
    End If
 End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question