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 or part :
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 :
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
The second row were excluded from to the searching ,because of there are the column headings in the second row.
When the result cells are active , the background color of these cells is yellow.
