Thread: Search Multiple Worksheets Against List of Non-Exact Search Criteria?

Hi folks,

I've spent a couple hours searching for a solution to this but came up empty handed. My macro/VBA skills are non-existent, so I hope that someone can help

Here's the situation...

I have a workbook with many many sheets in it.

The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so).

If matches are found (they don't have to be exact case), then I'd like two things to happen:

1. The rows containing the matched search criteria in the first sheet are highlighted.

2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.

I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms. Hope that helps somewhat in explaining the above!

Thanks in advance.

Best,
Ian

p.s. Just realized that the title of this post is misleading.. it's not 'non-exact search criteria', but rather, 'case insensitive search criteria'.

Welcome to the forum.

Try this:

Code:

Sub x()
    Dim cell        As Range
    Dim rFind       As Range
    Dim wks         As Worksheet
    Dim sAddr       As String
    Dim iOfst       As Long

    With Sheet1
        .UsedRange.Offset(1, 1).Clear
        For Each cell In .Range("A3", .Cells(.Rows.Count, "A").End(xlUp))
            iOfst = 0
            If Len(cell.Text) Then
                For Each wks In ThisWorkbook.Worksheets
                    If wks.Index <> Sheet1.Index Then
                        Set rFind = wks.Cells.Find(what:=cell.Value, _
                                                   After:=wks.Range("A1"), _
                                                   LookIn:=xlValues, _
                                                   LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
                        If Not rFind Is Nothing Then
                            sAddr = rFind.Address
                            Do
                                iOfst = iOfst + 1
                                With cell.Offset(, iOfst)
                                    .Hyperlinks.Add Anchor:=.Cells(1), _
                                                    Address:="", _
                                                    SubAddress:=rFind.Address(, , , True), _
                                                    TextToDisplay:=wks.Name
                                End With
                                Set rFind = wks.Cells.FindNext(rFind)
                            Loop While rFind.Address <> sAddr
                        End If
                    End If
                Next wks
            End If
        Next cell
    End With
End Sub