How do I write a Search Function into an Excel Spreadsheet?

[RiyaadhI]

Hi there.

I require assistance writing a search box into a spreadsheet so that when I type in a value, it automatically returns the data linked to that value I typed.

For example, I have a spreadsheet with multiple tabs with my company`s part number in column A, with the industry cross-referenced part numbers in columns B, C, and D. Sometimes there is only an alternate number in column B and sometimes in multiple columns.

I want to create a search box for my customers so that all they have to do is input my part number into the box and the corresponding part numbers in columns B, C, and D will pop up.

Is something like this possible?

I know the ctrl+F function will help take the user to that particular cell, but I would like to take the extra work out of finding the cross-referenced part number.

 

[blackgolf]

Hi Riyaadhl
1. Will you have the same part number in Column A somewhere else or will you have duplicates?
2. Do you want the search to search all sheets in your workbook?
3. Will column H be your last column in all other sheets too?

 

[blackgolf]

Also, if you want the search to be done on all sheets, and your part number on all sheets is in Col A, and cross referenced part numbers in Col B, C and D, then I would add a SEARCH sheet.
On the search sheet I will have a Cell to input the part number of any sheet (CV Boots, CV Joints, Modules, Coils etc), then use VBA and with a SEARCH button, enter the part number and click on your button to get the cross referenced part numbers.

 

[RiyaadhI]

Hi blackgolf,

Thank you for your reply.

1. Will you have the same part number in Column A somewhere else or will you have duplicates?
The part number in A2 will only appear in that cell across all the spreadsheets.
2. Do you want the search to search all sheets in your workbook?
Yes.
3. Will column H be your last column in all other sheets too?
The number of columns will vary according to the number of cross-references available for my part number in Col A.

 

[blackgolf]

Sorry, lastly I see the part numbers in Col A is similar to Col D and Col G, so must it search in these columns as well as you said it must search only in Col A?
The Headers are also the same for Col A D G = Stock and Col B E H = X_REF

 

[blackgolf]

Riyaad, try the following VBA Code: It will search for each value you placed in Cell D8 in all the workbook sheets. It will then place it from Cell D12 onwards..
Sub SearchAndCopyValue()
' Set the search value
searchValue = Sheets("Search Parts").Range("D8").Value

' Set the target sheet for pasting the value
Set targetSheet = Sheets("Search Parts")

' Set the target column for pasting the value
targetColumn = targetSheet.Cells(11, targetSheet.Columns.Count).End(xlToLeft).Column + 3

' Loop through all sheets in the workbook
For Each sourceSheet In ThisWorkbook.Sheets
' Skip the "Search Parts" sheet
If sourceSheet.Name <> "Search Parts" Then
' Find the search value in Column A of the current sheet
Set foundRange = sourceSheet.Columns("A:A").Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)

' If the search value is found
If Not foundRange Is Nothing Then
' Determine the last row in the current sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

' Set the range to copy
Set copyRange = sourceSheet.Range(foundRange, sourceSheet.Cells(foundRange.Row, lastRow))
Set copyRange = copyRange.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

' Determine the target row for pasting the value
targetRow = targetSheet.Cells(targetSheet.Rows.Count, "D").End(xlUp).Row + 1

' Copy the values to the target sheet starting from D12 or below
If targetRow < 12 Then targetRow = 12
copyRange.Copy targetSheet.Cells(targetRow, targetColumn)
End If
End If
Next sourceSheet
End Sub

The following code will clear your previous search, so run this before you do your new search:
Sub ClearCells()
' Set the target sheet
Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Sheets("Search Parts")

' Determine the last row in column C
Dim lastRow As Long
lastRow = targetSheet.Cells(targetSheet.Rows.Count, "C").End(xlDown).Row - 1

' Clear the contents of cells from C11 to the last row
targetSheet.Range("C10", targetSheet.Cells(lastRow, targetSheet.Columns.Count)).ClearContents
End Sub

Let me know if you are not familiar with running macros, then I will assist.

 

[RiyaadhI]

Thank you Sir/Madam.

Much appreciated.