Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to find text in Excel (2002)

    I am trying to record a macro that will find text in an Excel workbook, but the Find function does not get recorded. What I would like is a macro to pop up the Find dialogue box, with the Workbook option selected, which then waits for user input. Can anyone help, please?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Unfortunately, there is no option to specify Workbook when invoking that dialog. You could use some VBA with a simple Inputbox to get the find value, unless you need to specify any other options too?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Input box

    Could you suggest how I go about that, please?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    For example:
    Code:
    Sub FindIt()
       Dim strFind As String
       Dim wks As Worksheet
       Dim rngFound As Range
       strFind = InputBox(prompt:="Enter string to find", Title:="Find what?")
       If Len(strFind) > 0 Then
          For Each wks In ActiveWorkbook.Worksheets
             Set rngFound = wks.UsedRange.Find(what:=strFind, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
             If Not rngFound Is Nothing Then
                Application.Goto rngFound, True
                Exit Sub
             End If
          Next wks
          MsgBox strFind & " not found."
       End If
             
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Rory. That certainly helps.
    Would there be any way of repeating the search to include all the sheets in a workbook or to give an option to find the next occurrence of the same string?

    StephenS

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The search will look in all sheets as written. It is certainly possible to write a FindNext type option in, but would it not be just as easy to use the Find box and manually tell it to search in the Workbook? (that setting will persist between finds in the same Excel session)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The problem is that I want to make the spreadsheet available as a search tool to people who, in many cases, have never used a spreadsheet before, and there might not be anyone available to instruct them. I was hoping that I could get a macro that would open automatically and then give them all the search results.

    StephenS

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you want all the results listed (without selecting each in turn and popping up a "do you wish to continue?" type message, then you'd need a form. I take it training them is not an option?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Training is not really an option as they will often be one-time visitors. I have tried a form but it only seems to search the active sheet. A "Do you wish to continue?" message would be OK, as long as it serched all sheets in the workbook.

    StephenS

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    A form should work as long as you loop. Demo with message box:
    Code:
    Sub FindAll()
       Dim strFind As String
       Dim wks As Worksheet
       Dim rngFound As Range
       Dim lngItems As Long
       strFind = InputBox(prompt:="Enter string to find", Title:="Find what?")
       If Len(strFind) > 0 Then
          For Each wks In ActiveWorkbook.Worksheets
             If FindIt(wks, strFind, lngItems) = False Then Exit For
          Next wks
       End If
       MsgBox lngItems & " matches found"
    End Sub
    Function FindIt(wks As Worksheet, strFind As String, lngMatches As Long) As Boolean
       Dim rngFound As Range
       Dim strFirstFind As String
       FindIt = True
       With wks.UsedRange
          Set rngFound = .Find(what:=strFind, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
          If Not rngFound Is Nothing Then
             strFirstFind = rngFound.Address
             Do
                lngMatches = lngMatches + 1
                Application.Goto rngFound, True
                If MsgBox("Found item. Do you wish to continue search?", vbYesNo) = vbNo Then
                   FindIt = False
                   Exit Do
                End If
                Set rngFound = .FindNext(rngFound)
             Loop While rngFound.Address <> strFirstFind
          End If
       End With
    End Function
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Rory,

    That is exactly what I was after! Thank you very much indeed.

    Best wishes,

    StephenS

  12. #12
    New Lounger
    Join Date
    Oct 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Almost

    Hi,

    This code is almost exactly what I need.
    I am searching large amounts of data and I am having just a few issues with it.

    When the code runs out of search items (from the user repetedly clicking yes) it gives an error message and the debugger states there is a problem with this line:

    Application.Goto rngFound, True

    I could do with the code looping back around and taking me to the first search item.

    The message box also only shows the number of search results according to the number of times the user has clicked "yes." Is there anyway it could just display the total numbers of the search in the spreadhseet?

    Your help on this would be greatly appreciated.

    Thanks.

  13. #13
    New Lounger
    Join Date
    Dec 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory,

    I came across your macro a month ago and have successfully incorporated it into a workbook with great success and functionality. Just prior to distributing the workbook for use by a user group in December 2012, the macro now is showing an error:
    Run-time error '1004':
    Method 'Goto' of object '_Application' failed


    The line that shows the error is : Application.Goto rngFound, True

    I have noticed that it does work intermittently, but always returns to the error.

    FYI: The only thing that I changed in the code was the input box message. The workbook has several worksheets, all of which are password protected.

    Can you help me?

    Thank you.

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Joey,

    Welcome to the lounge as a new poster!

    What version of Excel are you using? If you are using 2007/10 when the password protection was setup on the worksheets did you uncheck the Select locked cells box?
    ProtectSheet.JPG
    If so check it and it should fix your problem.
    Last edited by RetiredGeek; 2012-12-06 at 16:42.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #15
    New Lounger
    Join Date
    Mar 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    Could you please let me know as what needs to be entered at (prompt:="Enter string to find"

    Thank you

    Regards
    Kiran Kumar

Page 1 of 2 12 LastLast

Posting Permissions

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