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]

Look here for what you need:

http://www.mrexcel.com/forum/showthread.php?t=23469

 

[rebelroy]

Hi Joe,

Fantastic link - my problem sorted. Thanks very much.

 

[sdhinternet]

This sounds like what I need, however I am crap at VBA, all I need is a button - which would then show a input box goto or find, so the user could type in a name or number - click ok and will show the data - if this makes sense, rather than scrolling through thousands of cells click to find and the search would take you to the cell - Please hellllllpppp

 

[Joe Was]

Did you check the link two posts up?

I think, I did it in there?

 

[SLKGVILLE]

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

This code works great!! How can I get the results to print to a new sheet everytime the search is run? When a new search is run I would like for the old results sheet to be deleted and a new one created. Is this possible? Thanks!!

 

[Grege]

Ok, This is exactly what i need however being a non VB person i am having some issues in applying the code...

So if sheet2 has the data, i have a text field on Sheet1 and i want the out puts to be on sheet 3

Data to search ranges from a1 to g50 on sheet 2 and i want to search column b for the data then as per the original export to sheet 3 in a range from row 5 say.

Realise this is rather basic but would appreciate the help.

Greg

 

[bkeller83]

I know this is an old thread, but I'm hoping to get a little guidance here. The macro below works perfectly. However, does anyone know how to modify it to search across multiple worksheets rather than searching only one worksheet?

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

 

[AJXX]

Hello Joe could you please help me ?
I'm trying to create create a search criteria using 2 text boxes, one where you type what you want to search and another one where you find the result, and a search button. The things that I want to search are located in the range of B8:C43 colums located on a different sheet. I've looked over your previous posts but I couldn't figure it out. I am rubbish at macros and even worst at VB. If you could explain the steps it would be great.
So if you could give me a hand I would really appreciate it.

Thanks in advance,
Sam

 

[AJXX]

Hello!!! Could anybody help me? Please.......

 

[Adele-Amy]

I am wanting to create a search box in Excel... I am a beginer at this so please be gentle with me.

I have an excel worksheet with 22 different pages.. my idea is to have my first page as a search box and the other 21 pages will have infromation about the different locations I work at.

I want someone to open the worksheet and be able to type in the name of the location and it will open that whole worksheet.

Please help