Find function in a macro

[Natalie]

Hi,

Is there any way that I can create a "find" button on my excel spreadsheet?

I have a lot of data for people that aren't familar with excel and I thought
adding a button with a macro on the actual worksheet would make it easier?

 

[Harald Staff]

Sure. How would you like it to behave ? Where shall the user enter her
search criteria ? How should the button respond to it ?

HTH. best wishes Harald

 

[Natalie]

Ideally I would like -

Cell A1 - Type in search critieria
Cell B1 - Search button to click after typing in criteria

Then

Any name matching the criteria to appear in the rows below?

Thanks!

 

[Myrna Larson]

What is already in the rows below 1? If it's your list, you don't want to copy
the found data over the existing data, do you? Where is the data that you want
to search?

 

[Harald Staff]

Ok, one approach:

Open the excel file in question.
Open the VB editor (Alt F11 or similar).
There, menu Insert > Module.
You will see a new blank module sheet. Paste this into it:

' ****** beginning of block *****
Option Explicit

Sub ButtonClick()
Dim S As String
Dim R1 As Range, R As Range
Dim Cbo As DropDown

S = ActiveSheet.Range("A1").Value
If S = "" Then Exit Sub
Set Cbo = ActiveSheet.DropDowns(1)
Cbo.RemoveAllItems
On Error Resume Next
Set R = Cells.Find(What:=S, _
After:=Range("A1"), _
LookAt:=xlWhole)

If R Is Nothing Then Exit Sub

Cbo.AddItem R.Address(False, False)

Do
Set R = Cells.Find(What:=S, _
After:=R, _
LookAt:=xlWhole)
Cbo.AddItem R.Address(False, False)
Loop Until R.Address = "$A$1"

Set Cbo = Nothing
End Sub

Sub CboSelect()
Dim Cbo As DropDown
Set Cbo = ActiveSheet.DropDowns(1)
ActiveCell.Activate
Range(Cbo.List(Cbo.ListIndex)).Select
Set Cbo = Nothing
End Sub
' ****** end of block *****

Now return to Excel and your sheet.
Go menu View > Toolbars, select the Forms Toolbar.
There is a button on it. Place a button over cell B1.

Assign the "ButtonClick" macro to it if asked. If not then do it manually by
rightclicking it and choose "assign macro"

There is also a ComboBox on it. Place it below the button. Assign the
"CboSelect" macro to it if asked. If not then do it manually by
rightclicking it and choose "assign macro".

And that's it. Enter something in A1, click the button and matching cells
are listed in the combobox. Select a cell address in the combobox and you're
transported to it on a magic carpet.

Change the text xlWhole to xlPart in the macro code if you want to search
for part of the cell content instead of whole cells.

HTH. Best wishes Harald