Find function in a macro
 

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?

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" skrev i melding
...
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?

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!

"Harald Staff" wrote:

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" skrev i melding
...
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?

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?


On Tue, 22 Mar 2005 07:11:10 -0800, "Natalie"
wrote:

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!

"Harald Staff" wrote:

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" skrev i melding
...
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?

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

"Natalie" skrev i melding
...
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!

"Harald Staff" wrote:

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" skrev i melding
...
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?