Home » Blogs » Nick's blog

XLA routines: EE_Find

Nick's picture
Submitted by Nick on 31 January, 2015 - 08:12
The problem with Excel's "Find" routine is that it does not reset the Find criteria, so that when you use CTRL+F on the worksheet, you have to reset all the params. EE_Find gets around this by resetting the criteria. Returns a range object or nothing 
Function EE_Find(strFind As String, rngRangeToFindIn As range) As range
'- takes a string, RangeToLookIn
'- returns a range of the first cell containing the string
'- uses .Find method, and looks for exact match, in whole cell
'- returns the 'exact match' checkbox back to unchecked

'http://excelexperts.com/xla-routines-eeFind    for updates on this function

    Set EE_Find = rngRangeToFindIn.Find(what:=strFind, LookIn:=xlValues, lookat:=xlWhole)
 
    Cells.Find what:="", _
               after:=ActiveCell, _
               LookIn:=xlFormulas, _
               lookat:=xlPart, _
               SearchOrder:=xlByRows, _
               SearchDirection:=xlNext, _
               MatchCase:=False, _
               SearchFormat:=False
    Cells.Replace what:="", Replacement:="", ReplaceFormat:=False
End Function
»