VBA to get "Find and Replace"

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

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

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

slack7639

Board Regular
Joined
Apr 19, 2016
Messages
57
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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?

Code:
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

Board Regular
Joined
Apr 19, 2016
Messages
57

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this instead:

Code:
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

Board Regular
Joined
Apr 19, 2016
Messages
57

ADVERTISEMENT

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

Board Regular
Joined
Apr 19, 2016
Messages
57
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

Board Regular
Joined
Apr 19, 2016
Messages
57
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

Board Regular
Joined
Apr 19, 2016
Messages
57
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top