Find and Replace defaults to Formulas only. I need it to look at VALUES.

[Fred Holmes]

In the attached screen capture the problem is illustrated far better than my words would ever do.

Attachment 195451



How can I get it to let me look in VALUES?

Thanks

Fred

[jmulqueen]

I don't believe you can change this without using a macro. Excel only displays values in that dropdown box when you are on the Find tab.

I found this answer at a different forum

http://www.excelbanter.com/showthread.php?t=165976


You could make a dummy workbook and put it in your xlStart folder. Have a
macro in that workbook that does a find (and sets all the stuff the way you
like). Then closes and gets out of the way.


Option Explicit
Sub auto_open()

Worksheets("sheet1").Cells.Find What:="", After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False

ThisWorkbook.Close savechanges:=False

End Sub

The workbook opens, does a find (to fix your settings) and then closes to get
out of the way.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But if you do an Edit|Find and change anything, then those changed settings will
be remembered.

[Fred Holmes]

Thanks for your prompt response, and solution. Yours will likely work in all instances, but requires far more in the way of keystrokes in this particular instance than my own. (Manually changing 7's to 9's and 1's to 7's).

In the future, I think I'll rely on the 'armstrong' method. All this macro stuff makes my head hurt. All I wanted was a nice obedient column of numbers, after all.

All the best, and thanks very much for the input. I believe that the Microsoft engineers just wanted to go home early the day they coded that part of the application, don't you?

[jmulqueen]

Yeah, I know that feeling. sort of like this xkcd comic http://xkcd.com/974/

It does seem odd that the functionality doesn't exist, as far as I can tell. Good luck to you.

[Fred Holmes]

I know some guys JUST like that. Cracked me right up. Thanks a lot for your help. I tried to give you two stars, but the powers that be... well, are.