How to find Values by Default??

jssherman

New Member
Joined
Apr 15, 2003
Messages
9
When I do a CTRL+F to find information, the OPTIONS always is set to Look In Formulas. Is there any way to get is to default to Look In Values?

thanks.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
Reposting my workaround from
http://216.92.17.166/board2/viewtopic.php?t=49529&highlight=personal

Here's how you can achieve the effect by utilizing the Personal.xls workbook. By following the 12 steps below, you can use Find to search for values with a Look in Values default instead of a Look in Formulas default, even if you used Formulas the last time you did a Find.

How to make Find default to Values instead of Formulas:

Step 1
Close any open workbooks.

Step 2
Create a new workbook.

Step 3
Go to the VBE, and in the Project Explorer, click on VBAProject(Personal.xls).
Notes (in case anyone reading this does not know):
To get to the VBE, click on Tools > Macro > Visual Basic Editor, or press Alt+F11.
If the Project Explorer is not visible, while in the VBE click View > Project Explorer, or press Ctrl+R.
If you do not already have a Personal.xls project created, then create one, and return to Step 1. See Excel’s on line help for creating a Personal workbook.

Step 4
While in VBE with the Personal.xls project selected, click Insert > Module, and paste this in:

Sub FindValues()
Application.Dialogs(xlDialogFormulaFind).Show , 2
End Sub

Step 5
Click View > Microsoft Excel, or press Alt+F11.

Step 6
Click on Tools > Customize > Commands tab.

Step 7
With the Customize dialog box open, click on Edit from the worksheet menu bar. This will pull down the Edit menu options, and at the same time, it will enable the "Modify Selection" button on the Commands tab of the Customize dialog box.

Step 8
Click "Find" on the File menu, then click the "Modify Selection" button on the Customize dialog box. In the Name field, you should see something like "&Find…". So far so good.

Step 9
Click "Assign Macro…", then click "PERSONAL.XLS!FindValues", and click OK, then click Close.

Step 10
Assuming you have the Standard toolbar visible on the sheet, click on the Find icon (it is a pair of binoculars), and repeat Step 8 and Step 9. One note, at this point, the Name field might say "Find" without the ampersand. That is normal.

Step 11
Click the "Close" button on the Customize dialog box.

Step 12
From the File menu, click Exit. If you are prompted to save changes to Personal.xls, choose Yes. If you are prompted to save changes for any other workbook (Book1.xls or whatever), choose No.

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

Now, your Personal.xls workbook will do the behind-the-scenes work to default the Look in to Values.
 

jssherman

New Member
Joined
Apr 15, 2003
Messages
9
That works great, except now I can't use the normal Find command. What if I want CTRL-SHIFT-F to be "FindValues", and CTRL-F to be regular find? Is that possible? It seems like when I create the macro and assign it to CTRL-SHIFT-F, after I run it once, CTRL-F no longer does anything, nor does the Find buttons. It's like I can get either one or the other, but not both. Any ideas?
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
You asked for a way to circumvent the Excel default of Formulas as the Find argument, which was what I provided. Now it sounds like you need to circumvent the circumvention, to have Values appear all the time unless you employ a macro to have Formulas be the displayed argument some of the time.

Try this, untested, but place this macro in a standard VBA module, and assign it to a shortcut key with Ctrl+Shift+F or whatever you like. It will show Formulas as the argument:


Sub MyFind()
With Application
.EnableEvents = False
.Dialogs(xlDialogFormulaFind).Show , 1
.EnableEvents = True
End With
End Sub
 

jssherman

New Member
Joined
Apr 15, 2003
Messages
9
Right, I got how that will work. But why can't I have the regular Find and the FindValue Macro? The problem with the workaround is that you can edit the worksheet with the Regular Find window open, but not with the ones from the Macros.

I just want CTRL-SHIFT-F to open findValues, without it changing anything in the regular Find command. Why does the format change the latter?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,895
Messages
5,766,978
Members
425,392
Latest member
Booknerd

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