Excel VBA Search in a Range

By | September 5, 2014

Define a Range in Excel by selecting a series of records and entering a name in the NameBox

Public Function IsInRange(RangeName As String, FindString As String)
 Dim Rng As Range

 With Sheets("Ranges").Range(RangeName)
 Set Rng = .Find(What:=FindString, _
 After:=.Cells(1), _
 LookIn:=xlValues, _
 LookAt:=xlWhole, _
 SearchOrder:=xlByRows, _
 SearchDirection:=xlPrevious, _
 If Not Rng Is Nothing Then
 'Application.Goto Rng, True
 IsInRange = True
 'MsgBox "Nothing found"
 IsInRange = False
 End If
 End With
End Function


If IsInRange("RangeSalesReturns", "FLR5") Then
 Msgbox("Found in range")
 MsgBox("Not found in range", False)
End If