Macro for Find and replace function

this should be possible without vba - in your find box, type dish*
and then in the advanced options check "Match entire cell contents"

if you really want the vba, i recorded this while doing the steps above and this seems to be doing what you want:

Cells.Find(What:="dish*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

Hi. If you have not done so you should consider putting this data in an Excel table and use Excel's default table search function. I know this does not directly answer your question but from the outside looking in it seems to be a much simpler solution than trying to reinvent the wheel.

you cannot simply put "de" you need to type in "de*". The * is called a wildcard and tells excel to look for anything beginning with de - it can end with anything else. (you can also search for "*er" and find anything which ends in "er") also, after hitting ctrl + f you need to press the Options button, and select "Match Entire Cell Contents". The macro handles that step with the line "LookAt:=xlWhole" so i'm surprised at your comment "the macro ... is finding all the cells have dish in it", that should not be happening if you copied that line exactly

Sorry Alpha_Mike,

I don't know what you mean by "Search box." but, if you search for "de*" and either select "Match Entire Cell Contents" in the find & replace window, or if you use the vba i provided and replace "What:="dish*"" with What:="de*" or some .value type reference to any range or input box or however you want to define your search parameter, then it should skip Powder and only pull in Detergent (or any other cell which starts with the letters de).

did something not work after trying either of those two methods? what did not work? Can you upload a sample workbook showing your code / "Search box" / dataset and demonstrating what about the solution is not working?