Search form on work sheet

[Ryan_Bernal]

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.

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!

[Admin]

Hi

Please find attached.

[Ryan_Bernal]

Thanks Admin!
The code you made is perfect!:cheers:
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:


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?

[Admin]

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 ?

[Ryan_Bernal]

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!:cheers:

[Ryan_Bernal]

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.