+ Reply to Thread
Results 1 to 2 of 2

Contunue Search through entire workbook and directory

Hybrid View

  1. #1
    Registered User
    Join Date
    03-26-2013
    Location
    philly
    MS-Off Ver
    Excel 2010
    Posts
    2

    Contunue Search through entire workbook and directory

    Hi
    I am trying to get the code that I worte to ask the user if they would like to contine searching the entire workbox for the value enter in the textbox.
    When I run the code it finds the value on the first spreadsheet, But this value could be on other spreadsheet within the workbook. How do I get the code to ask once it finds it on the
    first spreadsheet to continue searching till it has search the whole workbook? Then I need it to search the whole directory to find if value exist on any other workbook
    I hope that I am explaining this right. I am not a person that writes code very well

    Below you will see my code

    Sub SearchAllSheets() 
        Dim ws As Worksheet 
        Dim rFound As Range 
        Dim strName As String 
         
        On Error Resume Next 
        strName = InputBox("Cross Ref#") 
        If strName = "" Then Exit Sub 
        For Each ws In Worksheets 
            With ws.UsedRange 
                Set rFound = .Find(What:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole) 
                If Not rFound Is Nothing Then 
                    Application.GoTo rFound, True 
                    Exit Sub 
                End If 
            End With 
        Next ws 
        On Error Goto 0 
         
        MsgBox "Value not found" 
    End Sub

  2. #2
    Registered User
    Join Date
    03-26-2013
    Location
    philly
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Contunue Search through entire workbook and directory

    I was able to get the code to prompt me to enter the data to do a search on. It searches the whole worbook.
    Now I am stuck on the following. Again I am reaching out for help because I am not a programmer.
    Anyway here is what I am trying to get this to do.
    - If possible the following:
    1. Prompt the tester “Enter Cross Ref # for search” – For example S.1.3-01
    2. Prompt the tester “What would you like to run this tool against? Current/All in Workbook/All in Directory (same as spell check tool)
    3. The macro will then search the worksheet for the following fields and results “nature of test” = duplicate; “cross ref” = # input at Step 1 (S1.3-01)
    4. The macro will copy all the rows containing the W/P Reference/Test Procedure/Result/Prepared fields from the S.1.3-01 worksheet to the row(s) in the duplicate worksheet.


    My Code:
    Sub SearchAllSheets()
        Dim ws As Worksheet
        Dim rFound As Range
        Dim strName As String
         
        On Error Resume Next
        strName = InputBox("Cross Ref#")
        If strName = "" Then Exit Sub
        For Each ws In Worksheets
            With ws.UsedRange
                Set rFound = .Find(what:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
                If Not rFound Is Nothing Then
                rFound.Select
                MsgBox "found one"
                    Application.GoTo rFound, True
                End If
            End With
        Next ws
        On Error GoTo 0
         
        MsgBox "Value not found"
    End Sub

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1