+ Reply to Thread
Results 1 to 21 of 21

a macro to find text based on user input

  1. #1
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    a macro to find text based on user input

    I am trying to create a simple macro that will find text based on user input. I tried recording a macro using the edit/find tool, but it won't let me go further with out entering the actual data. For example, I have a worksheet containing 1000 Last names, I want the user to be able to find any last name by entering a value as the search criteria.

    I can write macros using the macro recorder, but may need more in depth help if the answer is visual basic.

    Any help would be greatly appreciated!
    Last edited by CDPRINT; 08-29-2010 at 10:39 PM.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: 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.

  3. #3
    Forum Contributor
    Join Date
    09-07-2009
    Location
    Hemet, CA
    MS-Off Ver
    Office 2007 Professional
    Posts
    111

    Re: a macro to find text based on user input

    You can also use find option to search for names that is located on your tool ribbon or use this line of code like this to call out the find dialog
    Please Login or Register  to view this content.
    Also Andrew-R
    Your code is missing an End If
    Last edited by ABabeNChrist; 08-23-2010 at 05:25 PM.

  4. #4
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    Re: a macro to find text based on user input

    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.

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: a macro to find text based on user input

    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".

  6. #6
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    Re: a macro to find text based on user input

    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

  7. #7
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: a macro to find text based on user input

    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.

  8. #8
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    Re: a macro to find text based on user input

    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!

  9. #9
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: a macro to find text based on user input

    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).

  10. #10
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    Re: a macro to find text based on user input

    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!

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,664

    Re: a macro to find text based on user input

    I understand what to do next time I show code
    Please edit your prior post to add CODE tags.
    Entia non sunt multiplicanda sine necessitate

  12. #12
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: a macro to find text based on user input

    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?

  13. #13
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    Re: a macro to find text based on user input

    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.

  14. #14
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: a macro to find text based on user input

    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?

  15. #15
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    Re: a macro to find text based on user input

    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.

  16. #16
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: a macro to find text based on user input

    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?

  17. #17
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    Re: a macro to find text based on user input

    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.

  18. #18
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    Re: a macro to find text based on user input

    [/[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]

  19. #19
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: a macro to find text based on user input

    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?

  20. #20
    Registered User
    Join Date
    08-05-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    22

    Re: a macro to find text based on user input

    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.

  21. #21
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: a macro to find text based on user input

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1