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)
Set Firstcell = Cells.Find(What:=Sheets(1).TxtSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
All results are listed as hyperlinks in the generated report sheet with different a function.This function’s codes :
For Each Worksheet In ThisWorkbook.Worksheets
If argCreateList = Worksheet.Name Then
Exit Function ‘ if found – exit function
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = argCreateList
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.
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.