Home > Excel Tutorial > Searching A Value Across An Entire Workbook

Searching A Value Across An Entire Workbook

        The searched value can be easily found in all the workbook with the textbox and option buttons that they are added to the workbook’s first sheet.

Through option buttons,value in textbox  can be searched as two types : whole and part .Codes for this :

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

All results are listed as hyperlinks in the generated report sheet with different a function.This function’s codes :

Function NewSheet(argCreateList)

    For Each Worksheet In ThisWorkbook.Worksheets

        If argCreateList = Worksheet.Name Then

            Exit Function ‘ if found – exit function

        End If

    Next Worksheet

    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = argCreateList

End Function

User can be reached to cells of results when clicked on this hyperlinks .Also when the result cells are active , the background color of these cells is yellow.

The second row were excluded from to the searching ,because of there are the column headings in the second row.

Download Example File

  1. Juzer
    August 28, 2016 at 8:06 am

    If i want to search only in one default sheet then where to do changes in VBA Code , please advice !!

    • netmerkez
      August 29, 2016 at 7:54 pm

      You must add these codes :

      For Each oSheet In ActiveWorkbook.Worksheets
      oSheet.Activate
      If oSheet.Index 1 Then
      Sheets(1).Activate
      Exit For
      End If

      Or download template from here: http://adf.ly/1dXV13

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: