Change find default from formula to value

[SuzanneT]

Hi All...

The find and find/replace default is to find in the formulas. I need to change this to default to values.

Anyone know how to do this?

Thanks for the help.

 

[nevillestoke]

Hi Suzanne,

I couldn't figure out what you were saying because when I conduct an Edit. Find and Replace (or use Control H) I am just prompted to enter a value to find and one with which to replace the found value. In the Excel Help I found the following information. Are you looking at something else?

Find or replace data
You can search for specific text or numbers that you want to review or edit, and you can automatically replace data you find. You can also select all of the cells that contain the same kind of data, such as formulas, or cells whose contents do not match the active cell.

To find data in a list, you can use filters to display only the rows that contain the desired data. For example, in a list that includes international sales data, you can display just domestic sales.

What do you want to do?
Find and replace text and numbers
Find text or numbers

Find and replace text or numbers

Select cells with special data
Select blank cells

Select cells that contain formulas

Select cells that contain constants

Select cells that contain comments

Select cells that don't match the active cell in the row or column

Use a filter
Find specific rows of data by filtering a list

 

[SuzanneT]

Clarification of my question

Let me clarify my question:

1. When press ctl F (for find) the menu options state that the find will
LOOK IN: formulas.

2. I need to change that LOOK IN default to Look in: values

Reason is my end users do ctrl f then type in a word like vitamin c and the find comes back saying "item not found". Ofcourse it's not in a formula.

I can do a macro to override so when they press ctrl f, the macro invokes the menu and changes the option. However, I would prefer to modify the actual option if possible.

Thanks all,
Suzanne

 

[Juan Pablo González]

I don't know of any way to do that (Change the default value). Sorry.

 

[Tom Urtis]

The VBA code line for that is

Application.Dialogs(xlDialogFormulaFind).Show , 2

You can make it part of your Personal workbook when clicking on Edit > Find or Edit > Replace.

 

[Ivan F Moala]

AFAIK you cannot ? change the default....
The Find Fnction is one of those functions that recalls it's last user settings.
You maybe better off using VBA

 

[Tom Urtis]

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.

 

[Juan Pablo González]

Here's how you can achieve the effect by utilizing the Personal.xls workbook. By following the 12 steps below...

Hey Tom, you're starting an Excel support group ? one step each day ?

Now seriously, nice suggestion.

 

[yoonus]

Re: Clarification of my question

Let me clarify my question:

1. When press ctl F (for find) the menu options state that the find will
LOOK IN: formulas.

2. I need to change that LOOK IN default to Look in: values

Reason is my end users do ctrl f then type in a word like vitamin c and the find comes back saying "item not found". Ofcourse it's not in a formula.

I can do a macro to override so when they press ctrl f, the macro invokes the menu and changes the option. However, I would prefer to modify the actual option if possible.

Thanks all,
Suzanne

Resolved:

Go to Ctrl+F
Click Option Button
Select Values in Look in Drop Down
Thank you

 

[mblacklock]

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.

Thanks for keeping this post up for so many years. My frustration level has just taken a nosedive! I only have one spreadsheet where this is an issue, but it is a constant issue. No More!