Find Function

[sargum]

Hi,

Is there any way to integrate the "Find" Dialogue box right into the
Excel file such that it is always visible?
I want users to be able to find a file based on a key word entry (much
like they would do with ctrl + F).

Please note that I do not want a Find/Replace function, just Find, No
replace. I mention this b/c I noticed that there is something out
there that is available that allows for a find/replace but I just want
Find.

Thanks.

S

 

[Roger Govier]

Hi Sargum

As I have your file for dealing with Filtering (as per your other thread), I
have written a small macro for you and attached it to a button on the sheet.
The file has been emailed to you.

Sub FindItem()

On Error GoTo FindItem_Error

Selection.Interior.ColorIndex = xlNone
Cells.Find(what:=Range("C1").Value, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
nextfind:
ActiveCell.Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Select Case MsgBox("Do you want to continue searching", _
vbYesNo Or vbQuestion Or vbDefaultButton1, "Find Details")

Case vbYes
Selection.Interior.ColorIndex = xlNone
Cells.FindNext(After:=ActiveCell).Activate
GoTo nextfind
Case vbNo
Selection.Interior.ColorIndex = xlNone
End Select

On Error GoTo 0
Exit Sub

FindItem_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure FindItem of Module Module1"

End Sub

 

[sargum]

Hi Sargum

As I have your file for dealing with Filtering (as per your other thread), I
have written a small macro for you and attached it to a button on the sheet.
The file has been emailed to you.

Sub FindItem()

On Error GoTo FindItem_Error

Selection.Interior.ColorIndex = xlNone
Cells.Find(what:=Range("C1").Value, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
nextfind:
ActiveCell.Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Select Case MsgBox("Do you want to continue searching", _
vbYesNo Or vbQuestion Or vbDefaultButton1, "Find Details")

Case vbYes
Selection.Interior.ColorIndex = xlNone
Cells.FindNext(After:=ActiveCell).Activate
GoTo nextfind
Case vbNo
Selection.Interior.ColorIndex = xlNone
End Select

On Error GoTo 0
Exit Sub

FindItem_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure FindItem of Module Module1"

End Sub

--
Regards
Roger Govier








- Show quoted text -

Thanks Roger, this is exactly what I was looking for!!

Sargum