Find with Ctrl+F in code?

robfo0

Active Member
Joined
Feb 19, 2002
Messages
281
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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
OH, i found why it wouldnt find my search in your code ivan, "Find entire cells only" was checked :p

the other questions still stand though! :)
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top