Find with Ctrl+F in code?

[robfo0]

Hi everyone,

I found Dave Hawley's code to start the Ctrl+F find box, but I'm have a few problems. I'm trying to select all the sheets so that the find will look through them all, but when i use this code:

Sheets(Array("Active", "Inactive")).Select
Application.Dialogs(xlDialogFormulaFind).Show

the search comes up empty handed. I have no idea why. I can see they are selected, and the data im searching for i can see on the sheet.

Also, is there a way to control this find more? I would like it NOT to search a certain column, say K for example.

Thanks!

[Dave Hawley]

Hi robfo0


Funny you should mention this as it's one of the topics I cover mt latest newsletter, due out next week. Anyway, rather than display the Find dialog you maybe better off simply using a inputbox to collect the word(s) to be searched for. Try the code below to get you started.


Sub FindString()
Dim wsSheet As Worksheet
Dim rFound As Range
Dim strFind As String

strFind = InputBox(Prompt:="Enter the word(s) you would like to find")
If strFind = "" Then Exit Sub
For Each wsSheet In ThisWorkbook.Worksheets
wsSheet.Range("K:K").EntireColumn.Hidden = True
Set rFound = wsSheet.UsedRange. _
Find(What:=strFind, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not rFound Is Nothing Then
Application.Goto rFound, Scroll:=True
wsSheet.Range("K:K").EntireColumn.Hidden = False
End
End If
wsSheet.Range("K:K").EntireColumn.Hidden = False
Next wsSheet

MsgBox "No match"
End Sub




It will search through each Worksheet looking for a match, but hides Column K before each sheet search(Find wont look in hidden rows/columns) then unhides when done. If a match is found the user will be taken straight there. You may want to change these arguments to suit.

LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False

At present it is looking in vales (not formulas), at whole cells (not part) and is NOT case sensitive.

[Ivan F Moala]

On 2002-03-08 22:34, robfo0 wrote:
Hi everyone,

I found Dave Hawley's code to start the Ctrl+F find box, but I'm have a few problems. I'm trying to select all the sheets so that the find will look through them all, but when i use this code:

Sheets(Array("Active", "Inactive")).Select
Application.Dialogs(xlDialogFormulaFind).Show

the search comes up empty handed. I have no idea why. I can see they are selected, and the data im searching for i can see on the sheet.

Also, is there a way to control this find more? I would like it NOT to search a certain column, say K for example.

Thanks!

The Find function within the Excel App is one
of those functions that not only holds it's
Value BUT is not Modal and calling it from a
VBA routine means you have to select the
range it works in......try using the ID
number which works for most versions of
excel eg.

Sub MySearch()

Sheets(Array("Active", "Inactive")).Select

Cells.Select
With Application
.CommandBars.FindControl(id:=1849).Execute
End With

End Sub


Ivan

[ This Message was edited by: Ivan F Moala on 2002-03-09 01:25 ]

[robfo0]

Thanks for the quick response guys, but I'm still having some problems.

Dave, your code works, but the reason i wanted to use the Ctrl F was in my sheets, there are some cells with simliar names, so i want the user to be able to go to the next match if the first wasnt the one they were looking for.

Ivan, can you explain the id a bit to me? ive never seen it before.

Another problem, when i select both sheets manually, then Ctrl F, and type for instance "windows", the search will find the first cell containing 'windows", but when i use any of the codes given (Dave's or Ivan's), the search only comes up with a match if the criteria is EXACTLY what is in the cell. Most of the time, the criteria im looking for isnt alone in a cell. Is it possible to change:
Find(What:=strFind,

and have:

Find(What:=*strFind*,

or something to that effect? i tried it, but i get errors.

Thanks again

[Ivan F Moala]

Use Daves.......

The code I submitted worked for me in finding
matches ??

Ivan

[Ivan F Moala]

On 2002-03-09 01:52, robfo0 wrote:
Thanks for the quick response guys, but I'm still having some problems.

Dave, your code works, but the reason i wanted to use the Ctrl F was in my sheets, there are some cells with simliar names, so i want the user to be able to go to the next match if the first wasnt the one they were looking for.

Ivan, can you explain the id a bit to me? ive never seen it before.

Thanks again

Each element of the CommandBars collection uses a unique number for its ID property. This number determines the built-in action for each control in the CommandBars collection....so identifying each command ID
will exercute that action eg try this;

Sub about()
Application.CommandBars.FindControl(ID:=927).Execute
End Sub


Ivan

[robfo0]

OH, i found why it wouldnt find my search in your code ivan, "Find entire cells only" was checked

the other questions still stand though!

[Ivan F Moala]

See above.......just missed your post

[robfo0]

Thanks ivan, thats why my first code wouldnt work hey? Well, I guess i could show the users how to use the Ctrl F manually, its just sometimes a pain, they ALWAYS find a way to mess something up, just trying to make it as painless as possible, for me and them