VBA MACRO FIND Selection

  • Hello guys! I need someone's help on this. I need an input box to search the values in column A.

    For example:

    Column A

    The big red fox
    Fox with nine red tails
    The red and cute fox
    Jump over the lazy dog

    From those 4 lines/rows i need the search to pick up the value i type in the input box.
    If i type "red fox" search tag includes the 1st-3rd rows. Meaning no matter where the value is as long as the red fox is present whether it is "fox red" or what ever.

    I have the input box macro but it only pick up the 1st one: "red fox"

    strFileName = InputBox("Please enter file name", "Word Search")
    If strFileName = vbNullString Then Exit Sub

    Rows(r).Select
    Set MyRange = Selection.Find(What:=strFileName, After:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    I hope you could help me with this.

    Thanks.

  • Re: VBA MACRO FIND Selection


    The below code will show all instances of your search word and display them in a message box. Adjust the ranges as needed. credit for the code goes to SMC. You can run this with a simple command button. :)



    Hope this helps.

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: VBA MACRO FIND Selection


    I'm not sure they caught the fact that you are loking to match multiple keywords, that not nescessarily in order or consecutive. Here is a simple worksheet formula based solution that will indicate if both keywords are present. Use FIND instead of SEARCH if you would prefer to be case-sensetive.

    =IF(ISERROR(AND(SEARCH($B$4,A7,1)>0,SEARCH($C$4,A7,1)>0)),"Not a match","Both keywords found")

    For a code based approach, you would want to do a .findnext loop similar to the code from SMC and store all the results for each keyword and then compare the two set of results. OR you could do a similar approach with instr().

    I prefer the worksheet based solution when possible.

  • Re: VBA MACRO FIND Selection


    Thank you for the reply guys.

    I need the macro to find the 2 words and paste it to another sheet. So the formula Demgar suggesting is ok but can you make it a macro type? I have like 200 entries and i want to find those key words both of it using a macro and transfer it to the next sheet.

    Yegarboy the macro didn't work it only copies the one word fox.

  • Re: VBA MACRO FIND Selection


    Ok, if you need a VBA solution, here you are. Just name the range with your data to be searched DataRange. I attached your example with this code working.


Participate now!

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