I use the Find command quite often in my company's workbooks (example: finding a project number from our project list by typing in the project name). Now, 99% of the time I am looking for something in a value, not a formula (I think this is probably true for most users). The "Find" Dialog Box always defaults to look in formulas, and I always have to manually change the dialog to look in values.

Does anyone know how to change the defaults for the dialog box to look in values? Or is there a way to get around this in a macro?

Thanks,

Aaron

hi Aaron,
sorry but I'm in a hurry, you can try with this:

- set up a macro with a shortcut association
[code]
WhatImlookingFor = InputBox(Prompt:="What are you looking for?", Title:="Find Values")
Dim X As Range
Set X = Cells.Find(what:=WhatImlookingFor, After:=ActiveCell, LookIn:=xlValues, _ lookat:= xlWhole)
MsgBox X.Address
[/cde]
This will provide the first cell which matches your search condition...of course you can refine it a lot...Enjoy.
FatherXmas

ops,
I forgot to say you need to run the macro using the previously defined shortcut...

FatherXmas,

Thanks for the code. This looks like it will the perfect solution! However, I tried it, and it got hung up in the debugger. This is my macro:

Sub Find_value()
'
' Find_value Macro
' Macro recorded 7/2/2004 by Aaron Coppersmith
'

'
WhatImlookingFor = InputBox(Prompt:="What are you looking for?", Title:="Find Values")
Dim X As Range
Set X = Cells.Find(what:=WhatImlookingFor, After:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole)
MsgBox X.Address
[/cde]
End Sub

The message that I got was:

Run-time error '91'
Object variable or With block variable not set.

Do you know how to get past this?

Thanks!

Aaron

Your probably getting this when X is Nothing

try putting an if statement in something like

If Not X Is Nothing Then
msgbox X.Address()
else
msgbox "address not found"
end if

HTH

David

There is an XL add-in that is perfect, and you can choose whether to look in cells with formulas or with constants. And it allows you to work with objects as well.

It is called FlexFind, by Jan Pieterse (hosted on Stephen Bullen's site)

http://www.bmsltd.co.uk/

(although his site is temporarily down).

Or try this site:

http://www.jkp-ads.com/Download.htm#Flexfind