Value find Dialog Box Macro

[Pauljj]

I have been using a code that pulls up the ctrl + F dialog box

Application.Dialogs(xlDialogFormulaFind).Show

How can I do this so the Find box looks for Values not Formulas, I thought it would be as simple as typing

Application.Dialogs(xlDialogValueFind).Show

but this option does not exist ?

Any ideas please

 

[erik.van.geit]

Hi,

see for example this thread
http://www.mrexcel.com/board2/viewtopic.php?t=96846
there might be still better if you do a search

anyway, the answer to your question is

Application.Dialogs(xlDialogFormulaFind).Show "", 2

kind regards,
Erik

 

[Pauljj]

Many thanks for that info

I have now used this code

Private Sub Find()

'

' Macro recorded 30/04/2007 by paulhai

Columns("A:A").Select


Application.Dialogs(xlDialogFormulaFind).Show "", 2


End Sub



I need to search within column A only, I thought this would work...but it doesn't it still searches across the sheet, where have i gone wrong ??

 

[erik.van.geit]

NOTE:
We are assuming you do not want to use the FINDmethod, but you want to get the findbox itself on your display.

this time I cannot get wiser using the helpfiles (which is an exception !!)
this is the result of my experiments
browse the internet for more
if you find usefull links, perhaps I could help more

Private Sub Find()

Columns("A:A").Select

Application.Dialogs(xlDialogFormulaFind).Show "123", 2, 2, 1
'arg1 = text to find
'arg2 = formulas (1) or values (2)
'arg3 = whole cells (1) or part (2)
'arg4 = searchdirection: column (1) or row (2)
'arguments
'text, in_num, at_num, by_num, dir_num, match_case, match_byte

'arguments for FIND
'see if there are "parallel" arguments ??
'What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

End Sub

I'll call some guys
EDIT: I did

 

[erik.van.geit]

German, French, Dutch, Italian, English o, my eyes I've been all over the world ... this is the result of another 30 minutes on the web

Private Sub FindBox()

Columns("A:A").Select

Application.Dialogs(xlDialogFormulaFind).Show "123", 2, 2, xlByRows, xlNext, True ',True
'arg1   text        What            text to find
'arg2   in_num      LookIn          formulas (1) or values (2) or comments (3)
'arg3   at_num      LookAt          whole cells (1 or xlWhole) or part (2 or xlPart)
'arg4   by_num      SearchOrder     xlByRows  (1) or xlByColumns (2)
'arg5   dir_num     SearchDirection xlNext (1) or xlPrevious (2)
'arg6   match_case  MatchCase       True or False
'arg7   match_byte  MatchByte       True or False

End Sub

I noticed the following differences
1. DialogBoxes are not exactly the same (code-Dialog has no listbox for worksheet or workbook, no replaceoption, ...)
2. Behaviour: selection has no influence when using code

weird: after executing code, Excel is instable when doing Control+F

 

[Pauljj]

Thanks Erik, your second code caused my excel to crash, the first, like mine, worked by selecting column A but just does not search within columm A, if i did it manually it does. I tried to record the macro but this doesn't work, how strange ??

 

[erik.van.geit]

Do you hate too when projects get unstable?
I got an answer from tusharm. He's more years of experience. Also pointed out that the boxes are different, that it was unstable ...

this is another approach

Sub FindIt()
Application.CommandBars.FindControl(ID:=1849).Execute
Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{UP}{UP}{UP}{DOWN}{ENTER}"
End Sub

tabbing to "formulas/values/comments"
3 times arrow up to be sure "formulas" is selected
1 time "down" to select the values

the 4 tabs could be replaced by the correct "ALt + key", but that would be "language"sensitive. (for me it is tab+k)

little problem:
I also hate SendKeys
but in this case, should be a very good alternative

best regards,
Erik