VBA to get "Find and Replace"

[slack7639]

I would like to set a button that opens up "Find and Replace" to search "By Columns" . . . tired of clicking to do this everytime . . . please see screenshot:

Find and Replace (a jpg on my Google Docs) . . . https://drive.google.com/open?id=1POdfmAQ8Ac2Uqj1yMiep9x6KytQpzu53

Any idea what the VBA code is to do this? Thx

 

[pgc01]

Hi

I've just tried the old CommandBars and still works in excel 2010. Try:

Application.CommandBars("Edit").Controls("Replace...").Execute

 

[slack7639]

That's close, thank you!

I changed "Replace" to "Find", but it doesn't switch it to "By Columns" (please see screenshot above)

When I record a macro to get it, "Find_By_Columns_B" below, it gives me that code . . .
. . . but no window to type in what I want
. . . also, I don't want to see the word "test"

Any idea how I combine these two?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Sub Find_By_Columns_A()

Application.CommandBars("Edit").Controls("Find...").Execute

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Sub Find_By_Columns_B()

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

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

 

[pgc01]

Hi

Not sure I understand exactly what you need, but you can use a bogus Find() just to set the parameters that you want.

For ex., in this case before invoking the dialog, I use a Find for an empty string (sets the find text in the dialog) and the option to search by columns.

I then execute the dialog and it has the find text box empty and the option to search by columns. Is this what you mean?

Sub Test()

' Execute this find just to set the default parameters of the dialog
Cells.Find what:="", _
               After:=ActiveCell, _
               LookIn:=xlFormulas, _
               LookAt:=xlPart, _
               SearchOrder:=xlByColumns, _
               SearchDirection:=xlNext, _
               MatchCase:=False, _
               SearchFormat:=False
               
' now execute the dialog
Application.CommandBars("Edit").Controls("Find...").Execute
End Sub

 

[slack7639]

Thats it! Thanks!

But, I have to click the "Options" button . . . then, I can see the expanded dialog box, and the cursor is there blinking, ready for me to type in something . . . any idea how you get it to open in that state? That would save a click.

 

[Scott Huish]

Try this instead:

Sub Test()

' Execute this find just to set the default parameters of the dialog
Cells.Find what:="", _
               After:=ActiveCell, _
               LookIn:=xlFormulas, _
               LookAt:=xlPart, _
               SearchOrder:=xlByColumns, _
               SearchDirection:=xlNext, _
               MatchCase:=False, _
               SearchFormat:=False
               
' now execute the dialog
Application.Dialogs(xlDialogFormulaReplace).Show

End Sub

 

[slack7639]

That works, and would work, but, it's not the same box I'm used to seeing . . . isn't it possible to get that?

I see that if I change "Replace" in this last line to "Find", that get's it closer to what I'm used to:
Application.Dialogs(xlDialogFormulaFind).Show
. . . I see that it lets me click to get to "Replace," but not back to "Find"
. . . and soon, this crashes Excel

Let me see if I can use VBA to do sendkey . . . but how do I click the Options button, and specify "By Columns" . . .

 

[slack7639]

Here's some progress, but . . .

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Sub Find_and_Replace()

' Use this macro to reduce the number of steps to bring up search "By Columns"

' Execute this find just to set the default parameters of the dialog
Cells.Find what:="", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False

' Now, execute the dialog
Application.CommandBars("Edit").Controls("Find...").Execute

' The dialog does not default open to "Find what:"
' It default opens with the focus on the "Find Next" button.
' You could click on the "Options" button at this point, to get there, but instead,
' automate this by using "Sendkeys" . . . Send: Tab + Enter . . . The tilde means "Enter"
' https://msdn.microsoft.com/en-us/library/office/aa202943(v=office.10).aspx
' https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-sendkeys-method-excel

SendKeys "{TAB}~", False

' Why does "Find Format" show up, after you close it, and click it again?
' This must have to do with the parameter settings section?

End Sub

 

[slack7639]

I figured out:

Sendkeys was going to the wrong place - that cell in the top right - and sending an "Enter", which was bringing up "Find Format"

Here's the current status:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Sub Find_By_Columns(control As IRibbonControl)

' Use this macro to reduce the number of clicks to bring up search "By Columns"

' First, run this "find", just to set the default parameters of the dialog:

Cells.Find what:="", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False

' Now, run the dialog:

Application.CommandBars("Edit").Controls("Find...").Execute

' For some reason, bringing this up in a macro doesn't give you a blinking cursor in "Find what"
' You can just start typing, though, it's working like it normally does.
' Alternatively, use Sendkeys to activate it - The simplest way might be to send a "Del"
' https://msdn.microsoft.com/en-us/library/office/aa202943(v=office.10).aspx
' https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-sendkeys-method-excel

SendKeys ("{DEL}"), True

' I'm not entirely sure on the "SendKeys" syntax.
' I think it's probably a good idea to have "True", so it waits.
' . . . True to have Microsoft Excel wait for the keys to be processed before returning control to the macro.
' . . . False (or omitted) to continue running the macro without waiting for the keys to be processed.
' It looks like you're supposed to write it this way, but the compiler rejects it:
' SendKeys ({DEL}, True)

' For some reason, bringing this up in a macro doesn't bring up the dialog "expanded" on the first try.
' You have to click "Options" to expand it, then, it stays this way, unless you close and re-open Excel.

End Sub

 

[slack7639]

Is there a way to get "Find and Replace" to open expanded on the "first" try, like you clicked the "Options" button?

You can do a "Sendkeys" of a tab and enter, but, since it has already been opened, the next time, it does those two keys, and goes to "Find Format"