Excel VBA search box single worksheet

[jonobono8888]

Hi All,

I have this workbook which contains multiple worksheets of data, with one key master worksheet ("Main Database") and with one worksheet called "Search Database" at the front.

The first worksheet "Search Database" has a search box/button configuration. So ideally, i'd type some words/numbers/text into the box and click the button and then it'll only search the worksheet named "Main Database" then print out anything that it matches with (or if it even contains that word/number). Ideally I'd like to keep the same formatting as how it is presented in "Main Database" - i.e. I've currently got conditional formatting on some of the cells and I'd like to retain that (mainly for Compliant/Non Compliant/Not Tested, etc).

The searching cell is B2. Below is some code I'm working with from a previous answer - currently it loops through all the spreadsheets.

• With regards to searching, I'd be looking into maybe 30 separate columns of data in the "Main Database" worksheet
• The "right spot" for the results would just be a table underneath the search area/button. Something like this:

[search box "B2"] [search button]

Results:
Column A (Business) | Column B (Device) | Column C (Project) | Column D (Assembly) | etc
...
...
..

Ideally those headers will always be there and can be in a "filter" mode, so that when any results come up, the filtering ability is available.

When doing a search - all the data will be in that row.


Here is the code:

'These constants represent the column numbers of each of the desired results
'change where necessary
Const colBusiness As Integer = 1
Const colDevice As Integer = 2
Const colProject As Integer = 3
Const colAssembly As Integer = 4
Const colAssemblyPart As Integer = 5
Const colComponent As Integer = 6
Const colComponentNum As Integer = 7
Const colClaiganNum As Integer = 8
Const colMatSup As Integer = 9
Const colMatType As Integer = 10
Const colMatInfo As Integer = 11
Const colCode As Integer = 12
Const colPatientCont As Integer = 13
Const colPatientDura As Integer = 14
Const colBiocompatibility As Integer = 15
Const colBioReport As Integer = 16
Const colEUMDRYR As Integer = 17
Const colSubsRep As Integer = 18
Const colEUMDR104 As Integer = 19
Const colEUMDR234 As Integer = 20
Const colReachSVHC As Integer = 21
Const colReachRest As Integer = 22
Const colCaProp As Integer = 23
Const colWEEE As Integer = 24
Const colROHS2 As Integer = 25
Const colROHS3 As Integer = 26
Const colEUPOP As Integer = 27
Const colPFOA As Integer = 28
Const colAUAsbestos As Integer = 29




Sub DoSearch()
'this code relies on a defined table existing on sheet1 and that it is the first table on the sheet
Dim shSearch As Worksheet, sh As Worksheet
Dim sSearch As String 'search term to find
Dim rFind As Range 'used to search
Dim sFirst As String 'used to record the address of the first instance of
Dim tblResults As ListObject
Dim NewRow As ListRow
Dim sName As String
Dim sFound As Boolean



Set shSearch = Worksheets("Search Database")
Set dbSearch = Worksheets("Main Database")

'set the table variable for easier coding
Set tblResults = shSearch.ListObjects(1)

'clear the table
On Error Resume Next
tblResults.DataBodyRange.EntireRow.Delete
' With tblResults
' .DataBodyRange.Rows(1).ClearContents 'clear first row
' 'Delete all other rows. Will error if none exist
' .DataBodyRange.Offset(1, 0).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
' End With
On Error GoTo 0

'get the search term, removing any leading or trailing spaces
sSearch = Trim(shSearch.Range("B2"))

'ignore blank search
If Len(sSearch) = 0 Then Exit Sub

'Loop through all sheets
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> shSearch.Name Then
'try to find
Set rFind = sh.UsedRange.Find(sSearch)

If Not rFind Is Nothing Then 'search is found
'record the first address
sFirst = rFind.Address

AddRow:
'Create a new row
Set NewRow = tblResults.ListRows.Add

NewRow.Range.Cells(1, 1) = sh.Cells(rFind.Row, colBusiness)
NewRow.Range.Cells(1, 2) = sh.Cells(rFind.Row, colDevice)
NewRow.Range.Cells(1, 3) = sh.Cells(rFind.Row, colProject)
NewRow.Range.Cells(1, 4) = sh.Cells(rFind.Row, colAssembly)
NewRow.Range.Cells(1, 5) = sh.Cells(rFind.Row, colAssemblyPart)
NewRow.Range.Cells(1, 6) = sh.Cells(rFind.Row, colComponent)
NewRow.Range.Cells(1, 7) = sh.Cells(rFind.Row, colComponentNum)
NewRow.Range.Cells(1, 8) = sh.Cells(rFind.Row, colClaiganNum)
NewRow.Range.Cells(1, 9) = sh.Cells(rFind.Row, colMatSup)
NewRow.Range.Cells(1, 10) = sh.Cells(rFind.Row, colMatType)
NewRow.Range.Cells(1, 11) = sh.Cells(rFind.Row, colMatInfo)
NewRow.Range.Cells(1, 12) = sh.Cells(rFind.Row, colCode)
NewRow.Range.Cells(1, 13) = sh.Cells(rFind.Row, colPatientCont)
NewRow.Range.Cells(1, 14) = sh.Cells(rFind.Row, colPatientDura)
NewRow.Range.Cells(1, 15) = sh.Cells(rFind.Row, colBiocompatibility)
NewRow.Range.Cells(1, 16) = sh.Cells(rFind.Row, colBioReport)
NewRow.Range.Cells(1, 17) = sh.Cells(rFind.Row, colEUMDRYR)
NewRow.Range.Cells(1, 18) = sh.Cells(rFind.Row, colSubsRep)
NewRow.Range.Cells(1, 19) = sh.Cells(rFind.Row, colEUMDR104)
NewRow.Range.Cells(1, 20) = sh.Cells(rFind.Row, colEUMDR234)
NewRow.Range.Cells(1, 21) = sh.Cells(rFind.Row, colReachSVHC)
NewRow.Range.Cells(1, 22) = sh.Cells(rFind.Row, colReachRest)
NewRow.Range.Cells(1, 23) = sh.Cells(rFind.Row, colCaProp)
NewRow.Range.Cells(1, 24) = sh.Cells(rFind.Row, colWEEE)
NewRow.Range.Cells(1, 25) = sh.Cells(rFind.Row, colROHS2)
NewRow.Range.Cells(1, 26) = sh.Cells(rFind.Row, colROHS3)
NewRow.Range.Cells(1, 27) = sh.Cells(rFind.Row, colEUPOP)
NewRow.Range.Cells(1, 28) = sh.Cells(rFind.Row, colPFOA)
NewRow.Range.Cells(1, 29) = sh.Cells(rFind.Row, colAUAsbestos)
'NewRow.Range.Cells(1, 5) = sh.Name & "[" & rFind.Address & "]"

'continue searching the sheet for more instances
Set rFind = sh.UsedRange.FindNext(rFind)
'if found in a different loaction than the first one, add that row too
If rFind.Address <> sFirst Then GoTo AddRow
End If



End If
'reset find variable
Set rFind = Nothing
Next sh

End Sub

 

[Akuini]

I answered a similar problem in this thread ( & there’s a sample workbook that you can download):

https://www.mrexcel.com/forum/excel-questions/1085320-search-entire-table.html

If that is similar to what you're looking for I think I can modify the code to suit your need.

 

[jonobono8888]

Hi Akuini,

I had a look at your file and worked through it. I think it's a fair bit different to that.
Is there a way I can attach a file for you to look at? Maybe that'll be easier?

 

[Akuini]

Hi Akuini,

I had a look at your file and worked through it. I think it's a fair bit different to that.
Is there a way I can attach a file for you to look at? Maybe that'll be easier?

You can’t attach a file in this forum, but you can upload your workbook (without sensitive data) somewhere (maybe via dropbox.com or google drive). Then put the link here.