Creating my own search box to find a keyword and then an option to find next.

bosmond

New Member
Joined
Sep 30, 2014
Messages
1
Good morning all, I am having some problems with some code.

I want to be able to search for keywords in a spread sheet with the option to find next. I understand that I can press CTRL-f but I would like to have a cell with a search box. I have done all I can to get this to work but so far all It will do is find one of many keywords and not find the next in the spreadsheet. Code is below. Can anyone help me with this?

Sub Search_Click()

'Opens box and ask what do they want to search
searchthis = InputBox("Go get it Benny.", "Property Search")
'Tells where to search
Columns("A:P").Select
'and then search in them whatever the user entered:
Selection.Find(What:=searchthis, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext).Activate

End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
Hello and welcome to the Board

Try something like this:

Code:
Sub bosmond()
Dim c As Range, searchthis, fa$
searchthis = InputBox("Go get it Benny.", "Property Search")
With Columns("a:p")
    Set c = .Find(searchthis, ActiveCell, xlFormulas, xlPart, xlByRows, xlNext)
    If Not c Is Nothing Then
        fa = c.Address
        Do
            c.Activate
            MsgBox "Press OK to find next:", vbInformation, "Found at " & c.Address
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> fa
    End If
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,471
Members
409,884
Latest member
Msinmath
Top