Searching A Value Across An Entire Workbook

Temmuz 27, 2016 Yorum bırakın

        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

Excel : Search Entire Workbook

Temmuz 22, 2016 Yorum bırakın

With Vba ,it is possible to search an entire workbook, for a searched value.

We will use The Find Method for this process.In Excel Program, The Find & FindNext Method is a very powerful option in finding data  and is useful.

When the macro runs , inputbox opens and asks the data we want to find. The found results are listed on the MsgBox  with cell addresses. The process continues until user  stops it.

 

Example File Can Be Downloaded Here

Takip Et

Her yeni yazı için posta kutunuza gönderim alın.

Diğer 58 takipçiye katılın