Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Find with Ctrl+F in code?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use Daves.......

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

    Ivan

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •