I can't test this without the workbook so try this on a backup file
Option Explicit
Private Sub Search_Click()
' finds and displays list of locations for strain entered in Search!D10, hyperlinks to drawer map
Range("C24:C500").Select
Selection.Clear
Dim findvar As String
Dim sht
Dim cl
Dim outputvar As Long
findvar = Sheets("SEARCH").Range("D10").Value
outputvar = 0
For Each sht In ActiveWorkbook.Sheets
Select Case sht.Name
Case ActiveSheet.Name, "Master List", "Pivot Table"
Case Else
For Each cl In sht.UsedRange
If InStr(cl.Value, findvar) <> 0 Then
Sheets("SEARCH").Range("C24").Offset(outputvar, 0).Formula = _
"=HYPERLINK(""#'" & cl.Parent.Name & "'!" & cl.Address & """,""" & cl.Parent.Name & " - " & cl.Value & """)"
outputvar = outputvar + 1
End If
Next
End Select
Next
Range("D10").Select
End Sub
Bookmarks