How to have Excel clear the Find and Replace box after a cell has been selected by the "Find Next" button

[TheJay]

Hello there, I need to search for numbers and after it has been highlighted on my spreadsheet, I would like VBA that clears the "Find what" box, so I can enter the next search query without messing about.

Could someone please tell me how this is achieved?

Thanks

 

[SanjayGMusafir]

Try this - I think it shall do the job for you

Sub ClearSearch

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate

End Sub

 

[TheJay]

Thanks Sanjay, what will force the code to run though? I added it to the sheet and tried a search and it didn't do anything.

 

[SanjayGMusafir]

Try to search any thing or Find Replace anything - then run this code and see if it clears the Find What or not

It is designed to work in background without popping any message

try it and update

 

[TheJay]

Okay, I inserted it into the sheet and then ran it manually. It just moved the focus to the next column and didn't clear the search query entered previously.

 

[SanjayGMusafir]

I think I forgot to mention one thing -

• If you insert this VBA on a particular sheet, it shall work on that sheet
• But if you insert it on General (for all sheets) it shall work universally
• See Screenshot
• I tried with my workbook it worked fine - as it is supposed to search after active cell (you can see this word in code)

 

[TheJay]

Thanks for your reply.

I'm not sure I understand. I don't know if I explained very well.

When I press Ctrl & F, I want the find box to appear but empty, so I can type something to search for without having to delete what I last searched for. I'm looking for a solution that is automatic, not involving pressing a button or going into the VBA and manually running the code.

 

[TheJay]

I've found a work around, but I cannot figure out how to get BACKSPACE to operate as part of a SendKeys combination.

Created a module

Sub ResetFind()
Application.CommandBars.FindControl(ID:=1849).Execute
Application.SendKeys.Send ("^a{BS}")
End Sub

Then bound Ctrl & F to that macro.

The search box comes up and if there is any text in it, it highlights it all and then it can be overtyped. I'd still like to know how to send BACKSPACE or DELETE after ctrl and a. What's wrong with my code please?

 

[TheJay]

I don't know if there is another way without using SendKeys, but adding VBA to a new module, one of the sheets or ThisWorkbook and then binding it to Ctrl + F works.

Sub ResetFind()
Application.CommandBars.FindControl(ID:=1849).Execute
Application.SendKeys "^a{BACKSPACE}"
End Sub