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!!

 

[MikeGauntlett]

How do I apply this to the raw data sheets i have with the search box on sheet 1 i really cant figure it out !

 

[thejesse]

This is a nice bit of code. A couple of questions I'm hoping someone can help with before I can use it though. First - If the following array is pasted in Sheet 2 starting at A4, the search behavior works properly for the term "test", but if "turkey" is searched for it only returns the row starting with soup.

apple	banana	test	turkey	tomato
soup	chickent	turkey	beans	test
yellow	rice	chocolate	mouse	hat

<tbody>
</tbody>

Next, how can I change this to search all worksheets instead of just Sheet 2? I fiddled with this but had no luck.

Finally, how can I make it so that it is not case sensitive?

Thanks in advance.

 

[aghunt19]

VBA works perfectly with my excel book, but I was wondering if anybody had an idea on how to modify the macro search to not be case sensitive. Is there anyway I can work it to use just key words?
Thanks in advance!

 

[sacksyboy]

This code is awesome, thank you.

I am having a problem with it...but it is because I am a novice with VB. My data is essentially a log that has rows added to it daily. My goal is to have a top row that searches for text and essentially filters the data based on the search criteria.

In yours, it outputs the data on a separate sheet. Mine needs to be the actual data on the sheet being searched so that once searched I can access the rows and update the data.

Is there a way to modify your code to do this?

This is a more advanced version 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