Rocky contacted me after studying the article The best way to use Excel’s discover function to spotlight or delete matching values. Like many Excel customers, Rocky would not use this function’s default settings, so he should change them, usually. The defaults do not appear user-friendly, and I additionally discover myself altering them usually.
If you happen to’re in the identical boat—you are a frequent person of Excel’s Discover function—you are not utterly caught. On this article, I will present you a easy macro that units this function’s default settings once you open the workbook. It isn’t a magic bullet, nevertheless it’s useful.
I am utilizing Workplace 365’s (desktop) Excel on a Home windows 10 64-bit system. This macro will work in earlier variations, however the on-line model would not help macros. You’ll be able to obtain the demonstration .xlsm file and the .bas module or enter the macro code manually. Do not attempt to copy and paste the code from this internet web page; the Visible Fundamental Editor (VBE) will complain about phantom characters which you can’t see.
SEE: System replace coverage template obtain (Tech Professional Analysis)
As a result of this text is about defaults, let’s assessment them shortly:
What specifies the search string.
Inside enables you to decide the place you search—the energetic sheet or the complete workbook. Sheet is the default. You need to change this feature to look the complete workbook.
Search determines the search path (type of)—by columns or by rows. You will not miss information by not altering this feature, however you would possibly velocity issues up a bit. By Rows is the default setting.
Look in enables you to restrict a search—to formulation, values, or feedback. Having the incorrect choice set can actually mess issues up, as I discussed earlier. Formulation (oddly sufficient) is the default. Use Formulation to shortly replace a reference.
Match case will discover solely these values that match the case used within the Discover what area. Disabled is the default.
Match total cell contents will discover solely these values that match solely the characters entered within the Discover what area. It is a good way to seek out actual matches or comparable matches, relying in your setting. Disabled is the default.
Format enables you to seek for particular codecs. That is extraordinarily useful if you happen to do not rely upon kinds; you should utilize Exchange to replace codecs with a easy search process. Disabled is the default. To entry choices, proven in Determine A, click on the Choices button. These choices will make it easier to fine-tune the duty:
If you use Excel’s Discover function, you will want to vary the settings in response to your process. Excel will bear in mind your settings, which might be helpful, or not. The one means to make use of this function effectively is to recollect to assessment the defaults each time you utilize it.
The macro in Itemizing A units the Discover function’s Look in choice once you open the workbook. It occurs behind the scenes. The macro depends on VBA’s Discover() operate, which automates the Discover function (Discover & Choose within the Enhancing group on the Residence tab). When utilizing the person interface, you reset defaults by selecting settings from a dropdown—generally altering them each time you utilize the function. Utilizing the FIND() operate, you cross those self same settings utilizing parameters, routinely.
Sub Auto_Open() 'Set Discover function's default LookIn choice when the file opens. Dim c As Vary Set c = Cells.Discover(What:="http://www.techrepublic.com/", LookIn:=xlValues) Finish Sub
To enter the macro right into a workbook, press Alt+F11 to launch the VBE. From the Insert menu, select Module. Enter the macro and return to Excel. If you happen to’re utilizing a ribbon model, remember to save the workbook as a macro-enabled file (.xlsm). After saving the workbook, shut and reopen it.
Opening the workbook triggers this straightforward macro, which units solely the What and LookIn parameters. There are lots of extra; this operate makes use of the next kind:
vary.Discover(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
the place vary is an expression that returns a Vary object. The parameters are just like the person interface function’s choices, however not the identical:
- First, there’s an additional one, After. This parameter specifies the (single) cell the place the search begins—it corresponds to the energetic cell when looking manually utilizing the dialog.
- Second, there is no WithIn parameter as a result of this operate searches vary. Within the case of our macro, vary is the Cells assortment.
Solely the What parameter is required; all others are non-obligatory and help the Variant information kind (the whole lot). Desk A lists the totally different constants for every parameter. The constants in daring are the defaults.
|LookIn||xlFormulas, xlValues, xlNotes|
|MatchCase||True for case-sensitive; False|
|MatchByte||True for double-byte character match; False (use just for double-byte language help)|
Discover that not the entire VBA arguments are the identical because the person interface. For example, as an alternative of Search, VBA makes use of SearchOrder; if you happen to use Search, VBA will return an error.
So as to add extra management, merely add the suitable parameters. For instance, the process in Itemizing B units LookIn, LookAt, and SearchOrder. Add it to a workbook, save the workbook as a macro-enabled file if obligatory, shut it, after which reopen it.
Sub Auto_Open() 'Set Discover function's default LookIn choice when the file opens. Dim c As Vary Set c = Cells.Discover(What:="http://www.techrepublic.com/", LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns) Finish Sub
Then, assessment the Discover choices by way of the person interface, as proven in Determine A, after opening the workbook.
The macro adjustments three default settings.
Not a magic bullet
For higher or worse, generally Excel appears to disregard the macro’s settings. For example, if you happen to use the Exchange keyboard shortcut, Ctrl+H, the settings will not be the identical as these you set utilizing the macro. The reality is, Exchange is not the identical as Discover. If you happen to use Ctrl+F to show the Discover dialog, you will discover the choices are the identical as these the macro set, till you utilize Exchange or Ctrl+H, then these settings usurp the macro’s. It is the way in which the function works—Excel saves the present choices and makes use of them till you modify them.
Rocky put the macro in a clean macro-enabled file and saved it in XLStart, so the macro is offered in every new workbook. If you do not know the way to discover XLStart in your system, learn Fast tip: Discover Excel’s XLStart folder quick. The article is previous, nevertheless it nonetheless works.
Ship me your query about Workplace
I reply readers’ questions after I can, however there is no assure. Do not ship recordsdata except requested; preliminary requests for assist that arrive with hooked up recordsdata will probably be deleted unread. You’ll be able to ship screenshots of your information to assist make clear your query. When contacting me, be as particular as doable. For instance, “Please troubleshoot my workbook and repair what’s incorrect” most likely will not get a response, however “Are you able to inform me why this system is not returning the anticipated outcomes?” would possibly. Please point out the app and model that you just’re utilizing. I am not reimbursed by TechRepublic for my time or experience when serving to readers, nor do I ask for a payment from readers I assist. You’ll be able to contact me at [email protected]