Creating a Search Box in Excel

[tommyb]

Hey guys.

I need some help in creating a search box in excel. I have 4 rows of data, and want the user to enter a 4 digit number in a text box, which is then searched in the spreadsheet, pulling up the results.

So far I have the following code..

Sub Button1_Click()
     
     'Opens box and ask what do they want to  search
    searchthis = InputBox("Type in a location keyword.", "Property Search")
     'Tells where to search
    Columns("A:E").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

It only highlights the results, leaving all other data on the spreadsheet. Ideally id want to see only the results of the search. Is there anyway to do this?.

Also, if the search returns no results, I get a debugger message. I want some sort of error message to come up.

Please help!!

 

[Joe Was]

It depends on what is OK for you?
You can hide and un-hide rows, change font colors from transparent to viable, Move the user to a new sheet and only show a copy of the found data, hide everything and only display the found data in a UserForm or TextBox or MsgBox...

What do you have in mind, for how you envision your application working?

 

[tommyb]

Ideally... Id want a text box in a spreadsheet.. and when a user enters data in the box and clicks a search button.. all instances of the text box is shown (the search string could be anywhere.. standing alone or imbedded within a string.. so for eg.. if someone searches for 123.. the result test123test also appears)

At the moment.. Im having to click a button.. which gives a pop up prompting me to enter a string.. and when I search for this.. the result is highlighted amongst the data in the spreadsheet (all data is located below the search button)

Hope this makes things a bit more clearer.

 

[Jonmo1]

Why re-invent the wheel? That functionality is already built into Excel. From Edit - Find.

You can specify to search the whole book, and to search partial strings.

It displays all results found in a nice list, hyperlinked to each cell containing the string.

 

[Joe Was]

How do you want the application to run?
From a Navagation sheet with the data on another sheet?
A Text box hard-coded static on a sheet?
One Big TextBox or a Self-sizing one on its own sheet?
Two TextBoxs one for the found data one for the search?
Do you want the data location or the actual data or both?
Is a custom UserForm better?
Can you build a UserForm if we help with the code?

 

[tommyb]

Hi Joe.

Ideally, Id like a text box on a blank worksheet.. and when the user enters text and clicks a search button.. the results are displayed under the text box (there will be more than 2 results per search).

Just want it to be a bit error proof. I want the user to know when nothing has been retrieved (either in a form of an error message.. or just a blank sheet.. not having a debugger pop up)

Hopefully this is a bit more clearer

 

[tommyb]

Can anyone help on this please

 

[Joe Was]

Sub CommandButton1_Click()
'Standard Sheet Module code, like: Sheet1.
Dim iFound
Dim Message$, Title$, Default$, myCode$

Message = "Enter data to search for:" ' Set prompt.
Title = "Get Data!" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
myCode = InputBox(Message, Title, Default)

iFound = False
Application.ScreenUpdating = False

'Check data sheet for data wanted!
Worksheets("Sheet1").Select
For Each r In Worksheets("Sheet1").UsedRange.Columns
n = r.Column

If Worksheets("Sheet1").Cells(1, n) = myCode Then
iFound = True
'Copy the found data from the starting row = myStart to
'the ending row = myFinish for the Found Date [myDate]
'to Sheet2 in column "C" change as needed!
'Start data import just below any entery in this column!
Worksheets("Sheet1").Range(Cells(2, n), Cells(4, n)).Copy _
Destination:=Worksheets("Sheet2").Range("C65536").End(xlUp).Offset(1, 0)
Else

End If
Next r

If iFound = False Then MsgBox "Date not Found!"
Worksheets("Sheet2").Select
Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub

 

[Joe Was]

This is a more advanced vertion of a search for and report found data macro:

Sub myFind()
'Standard module code, like: Module1.
'Find my data and list found rows in report!
Dim rngData As Object
Dim strDataShtNm$, strReportShtNm$, strMySearch$, strMyCell$
Dim lngLstDatCol&, lngLstDatRow&, lngReportLstRow&, lngMyFoundCnt&

On Error GoTo myEnd
'*******************************************************************************
strDataShtNm = "Sheet2" 'This is the name of the sheet that has the data!
strReportShtNm = "Sheet1" 'This is the name of the report to sheet!
'*******************************************************************************
Sheets(strReportShtNm).Select
Application.ScreenUpdating = False

'Define data sheet's data range!
Sheets(strDataShtNm).Select

With ActiveSheet.UsedRange
lngLstDatRow = .Rows.Count + .Row - 1
lngLstDatCol = .Columns.Count + .Column - 1
End With

Set rngData = ActiveSheet.Range(Cells(1, 1), Cells(lngLstDatRow, lngLstDatCol))

'Get the string to search for!
strMySearch = InputBox("Enter what to search for, below:" & vbLf & vbLf & _
"Note: The search is case sensitive!", _
Space(3) & "Find All", _
"")

'Do the search!
For Each Cell In rngData
strMyCell = Cell.Value

'If found then list entire row!
If strMyCell = strMySearch Then
lngMyFoundCnt = lngMyFoundCnt + 1
ActiveSheet.Rows(Cell.Row & ":" & Cell.Row).Copy

With Sheets(strReportShtNm)
'Paste found data's row!
lngReportLstRow = .UsedRange.Rows.Count + .UsedRange.Row
ActiveSheet.Paste Destination:=.Range("A" & lngReportLstRow).EntireRow
End With
End If
Next Cell

myEnd:
'Do clean-up!
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets(strReportShtNm).Select

'If not found then notify!
If lngMyFoundCnt = 0 Then
MsgBox """" & strMySearch & """" & Space(3) & "Was not found!", _
vbCritical + vbOKOnly, _
Space(3) & "Not Found!"
End If
End Sub

 

[rebelroy]

Hi all,

Sorry for cutting in on this thread but I'm looking for some code for a much simpler search box and I'm thinking Joe's solution if adapted might suit. I want to put a the box at the top of my worksheet with a 'Go' button alongside it. The Range to be searched is C21:C385. If a match is found I want the sheet to scroll to the match and make the cell active. If no match is found a message box should display with an appropriate message. Thanks.