Code Find All Function Of Find

  • Hi,
    I need to design a macro which will basically automate the "Find All" function so that other users can perform a search on a sheet.


    e.g. equivalent to going Edit, Find, "search term", Find All, and then being able to click on the desired result from the list displayed and be taken to that cell.


    I've spent about 4 hrs getting nowhere with this - when I try to record a macro of myself performing this action using Edit,Find the macro is blank and contains no code at all. If I record using "Find Next" instead of Find All, there is code but a) it doesn't actually execute and take you to the cell when you run it by macro and b) I really need the list of results that users can choose from.


    Any help greatly appreciated...

  • Re: Coding 'Find All' function in VBA


    Is all the data in one sheet, is there just one row or different rows?


    Have you considered using [af]*[/af] or [advf]*[/advf] instead of the [fm]*[/fm]


    If it's in only one column this will work better, if it's in more than one that may be a problem (for me to code anyway)?

    ________________________________________


    Good Luck!


    Ray :nana:

  • Re: Coding 'Find All' function in VBA


    Hi,


    Thanks for responding ... all the data is in 1 column on the one sheet, so hopefully that will make it easier.


    I don't think a filter would work as it will just be used to search for a key word which could be anywhere in any of the cells.


    Basically, I've got a huge list of FAQs that I need to make available to people, who can then quickly search through them using a key search word.
    Using Excel as I know more about it than anything else (tho not as much as I thought I did!) but if you can suggest an easier alternative - Word, html doc. or etc then cool.


    This will teach me to commit to doing something at work before checking that I actually know how to do it!!


    Regards,
    Dave

  • Re: Coding 'Find All' function in VBA


    Hi,


    i hope this will get you started


    Function Instr is case sensitive, so you need to modify the code as you wish.
    Better create userform to select search options.


    rgds,
    jindon

  • Re: Coding 'Find All' function in VBA


    Dave I can write something or you can play with the Macro recorder and develop your won solution.


    ________________________________________


    Good Luck!


    Ray :nana:

  • Re: Coding 'Find All' function in VBA


    Dave I can write something or you can play with the Macro recorder and develop your own solution. Assume you are searching column A


    ________________________________________


    Good Luck!


    Ray :nana:

  • Re: Coding 'Find All' function in VBA


    Hi,


    Well this may be your lucky day...and someone owes me a beer! Try this code for a 'find all' with search capabilities.


    Sub 'find_all' searches, finds a maximum of ten instances and reports back into cells A1/B1 to A(x)/B(x) and asks for the address to go to using the <F5> GoTo key. (It clears the A(x)/B(x) range first, of course). Kinda clunky, really, but suitable for some, I guess.


    Sub 'find_all2' searches, finds a maximum of ten instances and reports back with an inputbox of numbered choices of where to go. Enter the number of your choice and p00f you're there!


    Probably more like what you're looking for.


    Although they both SELECT the cells they look for/at (therefore wasting milliseconds) I've spent too many _hours_ writing and debugging them for now. If anybody wants to <tighten> them up by using 'Set = ' instead of .Activate in the search code, go for it. Just please post it back for me <g>. And,of course, if you plagiarize - give me a credit.



    and ...




    Cheers,


    dr

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!