Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've got a very large data sheet that has 5 sheets each with 65K rows of data and about 128 columns of data in each row. This data came out of and oracle database dumb due to the DB being decommissioned.

    I know I can use a control F to search the workbook for specific strings of text, however some users are not familiar with using control F and don't know how to change the settings to search the workbook instead of the sheet. (pretty basic stuff I know)

    To help with this problem, I'm trying to create a simple search tool that can search every sheet looking for a specified string of text. Currently I have a basic user form with a text box for the string of text. Below is the code I'm using to try and make this work but not having that much success.

    Ideally if I can't find the string of text in the first sheet, I want to continue the search on the subsequent sheets until I eaither find a match or have search through all 5 sheets. Any help with this would be appreciated.
    Thanks in advance.


    S_C = TextBox1.Value
    Dim intPos As Long
    For Each Sheet In ActiveWorkbook.Sheets
    Cells.Find(What:=S_C, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    intPos = InStr(1, ActiveCell.Value, S_C, vbTextCompare)
    If intPos = 0 Then GoTo NextSheet
    If intPos <> 0 Then Exit For
    NextSheet:

    Next Sheet

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='b0bito' post='775402' date='15-May-2009 14:05']Ideally if I can't find the string of text in the first sheet, I want to continue the search on the subsequent sheets until I eaither find a match or have search through all 5 sheets.[/quote]
    Try:
    Code:
    Sub SearchMultiSheets()
     Dim SrchTrm As String
     Dim i As Long
    	 
    	 SrchTrm = InputBox("Enter the string to be found.", "Workbook Search")
    	 For i = 1 To Worksheets.Count
    		 Worksheets(i).Select
    		 On Error Resume Next
    		 Cells.Find(What:=SrchTrm, After:=ActiveCell, LookIn:=xlFormulas, _
    			 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    			 MatchCase:=False, SearchFormat:=False).Activate
    		 If Err Then
    			 On Error GoTo 0
    		 Else
    			 Exit Sub
    		 End If
    	 Next i
    	 MsgBox """" & SrchTrm & """ could not be found in " & ActiveWorkbook.Name & "."
     End Sub
    Note: To require a match with the complete cell:
    Change "LookAt:=xlPart,"
    To "LookAt:=xlWhole,"
    Regards
    Don

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is a routine I have found useful (I have had it for a number of years and have no idea any longer who wrote it or where it came from exactly). I have it in my Personal.XLS and have a toolbar button for it

    Steve
    [codebox]Option Explicit
    Sub FindAcrossAll()
    Dim bDoIt As Boolean
    Dim sWhat As String
    Dim wks As Worksheet
    Dim sMsg As String
    Dim sTitle As String
    Dim iResponse As Integer
    Dim lStyle As Long
    Dim rFound As Range
    Dim sFirstAddress As String

    bDoIt = True
    While bDoIt
    sWhat = InputBox("What are you looking for?")
    For Each wks In Worksheets
    wks.Activate
    Set rFound = wks.UsedRange.Find(sWhat)
    If Not rFound Is Nothing Then ' The value has been found.
    sFirstAddress = rFound.Address
    Do
    rFound.Activate
    sMsg = "Continue the search ?"
    sTitle = "Continue ?"
    iResponse = MsgBox(sMsg, vbYesNo + vbQuestion, sTitle)
    If iResponse = vbNo Then ' Doesn't want to continue
    MsgBox "Search cancelled by user."
    Exit Sub ' Quit the macro
    End If
    Set rFound = Cells.FindNext(After:=ActiveCell)
    If rFound.Address = sFirstAddress Then Exit Do
    Loop
    End If
    Next wks
    If rFound Is Nothing Then ' Nothing found
    sMsg = "Not found! Do you want to start a new search?"
    lStyle = vbYesNo + vbExclamation + vbDefaultButton2
    Else
    sMsg = "Search complete. Do you want to start a new search?"
    lStyle = vbYesNo + vbDefaultButton2 + vbExclamation
    End If
    sTitle = "Search Complete"
    iResponse = MsgBox(sMsg, lStyle, sTitle)
    If iResponse <> vbYes Then bDoIt = False
    Wend

    iResponse = MsgBox("Search has ended.", vbInformation)
    End Sub[/codebox]

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You may want to check out my free Flexfind tool:
    www.jkp-ads.com/officemarketplaceff-en.asp
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks. Everybody provided some great tips that were very helpful.

Posting Permissions

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