Search field in excel

[wickstromhelena]

Hi!

I have a document with a number of questions and answers that will continue to grow. I want to set up a search macro so that it is possible to:
1. Search for a word/words (case insensitive) in a text box/ dialog box and by pressing return or similar the macro will search all questions for the word.
2. The macro should return and display all questions containing the word/words. the words should not be required to come in order in the questions.

I have found the following code and want something similar, BUT this code required that the words are entered in exactly the same order as it is stated in the question, and it does only mark where the question is. I want it to show ONLY the relevant question and filter away those that are not containing the search word.

Sub Find_Data()
' Written by Barrie Davidson
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub


I would be very grateful if someone could please answer my question ASAP!

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use Advanced Filter. See the last example here:

http://www.contextures.com/xladvfilter02.html