a macro to find text based on user input

Try this, which uses an input box to get the search criteria (I wasn't sure how you wanted the user to input it)

Please Login or Register  to view this content.

I have not done a macro using VB, only the macro recorder. While I understand the code you've written, I am not clear how or where to enter it to create a new macro.

Obviously a newbie here.

Press Alt-F11 to open the Visual Basic editing window.

From the Insert menu select module, a large, mainly blank, window will open up in the right hand pane and the code can be pasted there.

As the previous poster pointed out, if you're using my code you'll need to insert another line reading "End If" between the "End If" that is there and the "End Sub".

Thanks, Andrew! I think I am getting close. However, when I run the macro, the search screen comes up fine, but when I enter a name, I get an error message which says "Run-time error '438, object doesn't support this property or method. Below is the code I entered per your response and adding the "end if". I'd appreciate any help.

Sub MatchUserInput()
Dim strSearchTerm
Dim FirstMatch As Range

strSearchTerm = InputBox("Please enter the name to find", "Search criteria")

If strSearchTerm <> "" Then
Set FirstMatch = ActiveSheet.Find(strSearchTerm, LookAt:=xlWhole)
If FirstMatch Is Nothing Then
MsgBox "That name could not be found"
Else
FirstMatch.Select
End If
End If
End Sub

Sorry, that's what I get for dashing off code without testing it. My apologies.

Change the line that reads

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

This one is tested.

Andrew...that's awesome! Works perfect. Can you answer two more questions for me?
1. Is it possible for the code to "Find Next" for more than one string of the same text. Example...there are 5 Smiths in the list, the code only finds for the first one, I would like it to find all of them.
2. Is it possible that when the text string is found it can be highlighted?

Thanks!

Sure, the following code will find all of the matches and highlight them in red:

Please Login or Register  to view this content.

PS When you're posting you should be code tags around your code - it makes it much easier to read (and it's one of the forum rules).

Thanks for the heads up...I've read the rules and think I understand what to do next time I show code. I used the code you sent, but when I run the macro, the "find" box comes up, but when the data is entered, nothing happens. Would you mind looking at it a second time? Thanks!

I tested it before I posted (once bitten, etc.) and it worked fine. It will no longer jump to the first match, just find all of the matches on the active sheet and highlight them red. Could you try searching for the same text using the standard CTRL-F functionality and check that is hasn't done this?

OK, I see what you are saying. I think I did a poor job of explaining myself. What I need it to do is to jump to each subsequent name in the spreadsheet, similar to the "Find Next" option when you hit CTRL-F. Please forget I ever mentioned highlighting as this makes a change to the spreadsheet format.

Ah, now it's getting tricky.

My macro can be easily modified to jump to each matched cell in turn, but it will do so faster than any human could read them, so I'm guessing that's not what you want.

How do you expect the user to indicate that they wish to move to the next cell?

What if the dialog box stayed open after the first find and had a "Find Next" button with an option to close the window after finding the desired cell? Similar to how CTRL-F in excel works.

It's a little more tricky than it sounds, to the extend that I'm forced to ask the obvious question ... why not just use CTRL-F?

Thanks for the tip, but when I tried this, I get "Compile Error: Invalid Outside Procedure". See area below in blue for the area excel highlights. Any ideas?

Please Login or Register  to view this content.

[/[QUOTE][/Yes, I know, sounds silly, but the end user of the workbook is very limited in computer skills and I'm looking for the most user friendly option, or perhaps I just need a macro that opens the CTRL-F window without having to use the toolbar.QUOTE]

If you want to go with just opening the find dialogue programmatically then ABabeNChrist's code is the one to use, but you'll have to enclose it in a sub-routine, e.g.

Please Login or Register  to view this content.

Of course now the issue is that you need some way of launching that ... some way even easier than just pressing CTRL-F. It can be set to open when the workbook is opened, or when the user changes the value of a certain cell, or a host of other options - what's your requirement here?

This spreadsheet is an existing student list. I want the user to be able to quickly find the student by last name so they can view the student info. Additionally, I want the user to be able to find on last name so that they can verify "new" students before they are added to the worksheet.

OK, so how would you like the user to launch the find dialogue? You could protect the whole sheet with student info on and add forms for "View student information" and "Add new student", but that would be a more sizeable job.