Kapat

Search Entire Workbook & List Result Cells

excel search entire workbook Excel VBA Search
Entire Workbook

In this template, we developed a different and useful data search method.
The searched value can be easily found in all the workbook using the textbox and option buttons that they are added to the workbook’s first sheet .

excel vba search entire workbook

Through option buttons,value in textbox can be searched as two types , whole or part : vba find xlpart xlwhole

If Sheets(1).OptionButton1 = True Then
Set Firstcell = Cells.Find(What:=Sheets(1).TxtSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Else
Set Firstcell = Cells.Find(What:=Sheets(1).TxtSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End If

The value in textbox is searched entire the workbook,the found results are listed on the msgbox with cell addresses. The process continues until the user stops it.

vba search find entire workbook

All results are listed as hyperlinks in the generated a new sheet (Report sheet) through different a function.This function’s codes : vba create new sheet

Function NewSheet(createsht)
    For Each Worksheet In ThisWorkbook.Worksheets
        If createsht = Worksheet.Name Then
            Exit Function ' if found - exit function
        End If
    Next Worksheet
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = createsht
End Function

User can arrive to cells of results when clicked on this hyperlinks :

Sub Create_Hyperlinks()
Dim LArray() As String
For Each cell In Sheets("New_Report").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If cell <> "" Then
LArray = Split(cell.Text, "!")
ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=LArray(0) & "!" & LArray(1)
End If
Next cell
End Sub

📥 Download sample workbook


Search Entire Workbook Using Inputbox

In our other template, we used an inputbox control to search for data. vba search entire workbook inputbox

VBA codes of this template : excel vba search value in workbook

Sub Search_Entire_Workbook()
    Dim oSheet As Object
    Dim Firstcell, NextCell As Range
    Dim WhatToFind As Variant, counter As Long, LastColumn As Long
              
WhatToFind = Application.InputBox("What are you looking for ?", "Search", , 100, 100, , , 2)
        
        If WhatToFind = "" Or WhatToFind = False Then
        MsgBox "There is no value to be searched", vbCritical, ""
        Exit Sub
        End If
                     
    If WhatToFind <> "" And Not WhatToFind = False Then
NewSheet ("New_Report") ' Report Page is generated with Function NewSheet(createsht)
Sheets("New_Report").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Clear
 
For Each oSheet In ActiveWorkbook.Worksheets
    oSheet.Activate
If oSheet.Name = "New_Report" Then
    Exit For:   End If
   
LastColumn = oSheet.UsedRange.Columns(oSheet.UsedRange.Columns.Count).Column
   
Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

On Error Resume Next
If Firstcell.Row = 2 Then
Set Firstcell = Range(Cells(3, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, LastColumn)).Find(What:=WhatToFind, LookIn:=xlValues)
End If

If Not Firstcell Is Nothing Then
Firstcell.Activate

With Sheets("New_Report").Range("A1")
.Value = "Addresses Of The Found Results"
.Interior.ColorIndex = 19
End With
Sheets("New_Report").Range("A:A").EntireColumn.AutoFit
Sheets("New_Report").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = oSheet.Name & "!" & Firstcell.Address(False, False)

Call Create_Hyperlinks  'Hyperlinks are generated in New Report Sheet

If MsgBox("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & Firstcell.Address & vbLf & "Do You Want To Continue?", vbExclamation + vbYesNo) = vbNo Then
Exit Sub: End If

While (Not NextCell Is Nothing) And (Not NextCell.Address = Firstcell.Address)
                    counter = counter + 1
Set NextCell = Cells.FindNext(After:=ActiveCell)
                    
If NextCell.Row = 2 Then
Set NextCell = Range(Cells(3, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, LastColumn)).FindNext(After:=ActiveCell)
End If

If Not NextCell.Address = Firstcell.Address Then
NextCell.Activate
Sheets("New_Report").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = oSheet.Name & "!" & NextCell.Address(False, False)

Call Create_Hyperlinks

If MsgBox("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & NextCell.Address & vbLf & "Do You Want To Continue?", vbExclamation + vbYesNo) = vbNo Then
Exit Sub: End If                            
End If 'If Not NextCell.Address = Firstcell.Address Then
                
Wend
End If
Next oSheet
End If
        
If counter = 0 Then
Sheets(1).Activate
[A1].Activate
MsgBox "The value not present in this workbook", vbCritical, ""
Application.DisplayAlerts = False
Sheets("New_Report").Delete
Exit Sub:     End If

MsgBox "Found: """ & WhatToFind & """ " & counter & " times." & vbCr & _
 vbOKOnly, WhatToFind & " found in these cells"
      
Set NextCell = Nothing:  Set Firstcell = Nothing
End Sub

📥 Download sample workbook