Clear Find Settings

**Reafidy** · Apr 2nd 2007

Hi All,

In microsoft word you can clear find criteria like below.

Code

With Selection.Find 
   .clearformatting 
   .Replacement.clearformatting 
   .Text = "" 
   .Replacement.Text = "" 
   .Forward = True 
   .Wrap = wdFindStop 
   .Format = False 
   .MatchCase = False 
   .MatchWholeWord = False 
   .MatchWildcards = False 
   .MatchSoundsLike = False 
   .MatchAllWordForms = False 
End With

Display More

Does anyone know how to do this with excel?

Thanks in advance.

**Trebor76** · Apr 2nd 2007

Re: Clear Find Settings

Hi there,

Have a look at Ken Pul's code at http://www.vbaexpress.com/kb/getarticle.php?kb_id=343 which is (hopefully) what your after.

HTH

Robert
: D

**Reafidy** · Apr 2nd 2007

Re: Clear Find Settings

Hi Robert,

Thanks for the reply.

Unfortunately thats not what I was looking for. That is help to clear formatting for cells.

I am trying to change the criteria of the find. Ie when you goto edit find there will be the previous finds options set. I want to change these with code but without doing a find.

Cheers,

RossVancouver · Apr 2nd 2007

Re: Clear Find Settings

would something like this work?

Code

Sub ClearFindSettings()


Dim WhereWasI As Range


    Set WhereWasI = Selection


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




End Sub

Display More

**Reafidy** · Apr 2nd 2007

Re: Clear Find Settings

Hi Ross,

Thanks for the reply.

That helps, however I could have sworn I remember reading a post here on ozgrid with a similar way to how you would do it in word as I posted at the start.

Maybe I am loosin it. Thanks again.

**Trebor76** · Apr 2nd 2007

Re: Clear Find Settings

Reafidy,

Sorry my earlier posting didn't meet your needs and while Aaron Blood's fanstatic Kickbutt VBA Find Function function at http://www.ozgrid.com/forum/showthread.php?t=27240 might not either, I'm sure it will do the job for you.

HTH

Robert

**Reafidy** · Apr 2nd 2007

Re: Clear Find Settings

Thanks for your help Robert.

While Aarons find function truly is a master peice, I think the key to my question is to manipulate the find settings without actually doing a find.

However, I am starting to think Im leading people on a wild goose chase. So I think ill use an alternative way.

Thanks again for all your help.

**Trebor76** · Apr 2nd 2007

Re: Clear Find Settings

No problem.

While it's probably not the most efficient way, try the following (change the variable type if string is not suitable):

Code

Dim strFindVariable As String
strFindVariable = "Find_Me"


Application.SendKeys ("^f")
Application.SendKeys (strFindVariable)

This simply leaves the Find and Replace dialog box open with strFindVariable in the Find_what: drop-down list.

HTH

Robert
: D

**Reafidy** · Apr 2nd 2007

Re: Clear Find Settings

Thanks Robert.

Clear Find Settings

Participate now!

Share