Results 1 to 6 of 6

Thread: Search form on work sheet

  1. #1
    Member Ryan_Bernal's Avatar
    Join Date
    Dec 2012
    Posts
    37
    Rep Power
    0

    Search form on work sheet

    Hello!
    Can somebody help me on this?
    I am having hard time to populate text box without using input form.
    I want to use input form on worksheet which i used to search data on my worksheets (database 2010,database 2011, database 2012.. etc)
    Here is my code using input box which i pasted on standard module.
    Code:
    Public DSO As Object
    Public DstRow As Long
    Public DstWks As Worksheet
    Public SrcWks As Worksheet
    
    Private Sub FindKeyword(ByVal Keyword As String)
    
      Dim LastRow As Long
      Dim Result As Range
      Dim Rng As Range
      Dim StartRow As Long
        
        StartRow = 2
        LastRow = SrcWks.Cells(Rows.Count, "B").End(xlUp).Row
        LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
        
          Set Rng = SrcWks.Cells(1, 1).CurrentRegion.Offset(1, 0)
          Set Rng = Rng.Resize(Rng.Rows.Count - 1)
          
            Set Result = Rng.Find(What:=Keyword, _
                                  After:=Rng.Cells(1, 1), _
                                  LookIn:=xlValues, _
                                  LookAt:=xlPart, _
                                  SearchOrder:=xlByColumns, _
                                  SearchDirection:=xlNext, _
                                  MatchCase:=False)
           A = Rng.Address
           If Not Result Is Nothing Then
             FirstAddx = Result.Address
               Do
                 If Not DSO.Exists(Result.Row) Then
                   DSO.Add Result.Row, DstRow
                   SrcWks.Rows(Result.Row).EntireRow.Copy Destination:=DstWks.Cells(DstRow, "A")
                   DstRow = DstRow + 1
                 End If
                 DstWks.Cells(DSO(Result.Row), Result.Column).Interior.ColorIndex = 6
                 Set Result = Rng.FindNext(Result)
               Loop While Not Result Is Nothing And Result.Address <> FirstAddx
           End If
          
    End Sub
    
    Public Sub FindKeywords()
    
      Dim Keys As Variant
      Dim Keyword As Variant
      Dim Keywords As Variant
      Dim Msg As String
      Dim R As Variant
      
        Set DstWks = Worksheets("View")
        Set SrcWks = Worksheets("Database 2010")
    
        If DSO Is Nothing Then
          Set DSO = CreateObject("Scripting.Dictionary")
          DSO.Comparemode = vbTextCompare
        End If
        
          Msg = "Please enter the keywords below. Separate multiple entries with a comma." _
              & " Wildcard characters * and ? can also be included."
        
          Keywords = InputBox(Msg)
        
            If Keywords <> "" Then
              DstRow = 21
              DstWks.UsedRange.Offset(20, 0).Clear
              Keywords = Split(Keywords, ",", Compare:=vbTextCompare)
                For Each Keyword In Keywords
                  FindKeyword (Keyword)
                Next Keyword
            Else
              Exit Sub
            End If
          
        Set DSO = Nothing
        Set Keywords = Nothing
        Sheets("View").Select
        Range("a21").Select
    End Sub
    Here is my work book:
    Thank you in Advance!
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Please find attached.
    Attached Files Attached Files
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member Ryan_Bernal's Avatar
    Join Date
    Dec 2012
    Posts
    37
    Rep Power
    0
    Thanks Admin!
    The code you made is perfect!
    I add new lines in workbook code so that i can use the search engine and add data form in one sheet.
    here is the new code:

    Code:
    Private Sub Workbook_Open()
        Dim x
        
        x = GETSHEETS(Array("view"))
        Worksheets("view").cmbSheetName.List = x
        Worksheets("view").cmbSearchName.List = x
        
        
    End Sub
    By the way is it possible to add link on the search results so that when i click, i will go to the exact location of the searched data?

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Thanks for the feedback.

    It is possible to add link. Try to search on Hyperlinks.add in the VB help files (Search within VBE window). Record macro while adding the hyperlinks and customise the code.

    BTW, even after the whole row is copied to the result sheet, why do you need to see the results in exact location ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Member Ryan_Bernal's Avatar
    Join Date
    Dec 2012
    Posts
    37
    Rep Power
    0
    Thanks for the suggestions.
    I am planning to make search engine for my folder directory that will output all the files in excel sheet with their links.
    This is like the search command on windows, but this time using excel.
    Happy New year!

  6. #6
    Member Ryan_Bernal's Avatar
    Join Date
    Dec 2012
    Posts
    37
    Rep Power
    0
    Hello Admin,
    Just want to know how to include another text boxes (criteria). So that i can filter only the data that i want base on the criteria.

Similar Threads

  1. Replies: 1
    Last Post: 06-12-2013, 07:42 PM
  2. INPUT DATA FORM ON WORK SHEET
    By Ryan_Bernal in forum Excel Help
    Replies: 4
    Last Post: 12-30-2012, 05:56 PM
  3. Copy Row To A New Sheet If A Value Found In Adjacent Column
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 08-17-2012, 05:42 PM
  4. work book format
    By Prabhu in forum Excel Help
    Replies: 1
    Last Post: 03-25-2012, 10:33 PM
  5. Replies: 2
    Last Post: 11-17-2011, 07:49 PM

Posting Permissions

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