Hi
I use this macro to search within defined ranges within several worksheets, for the text string contained in the current selected cell when macro is triggered.
I need a 'find' box to open at start of macro, so I can input the text string I want to search for, then the macro continues to search within the defined worksheet ranges.
Any help very much appreciated...
thanks
Code
Sub FindInLists()
'
' Select FindInLists Macro
'
Dim SheetsToSearch, x As String, ws As Excel.Worksheet, r As Range
SheetsToSearch = Array("List1", "List2", "List3") '// Enter the exact sheet names of sheets to be searched
x = ActiveCell.Value 'set the value of the activecell, unique ref number cell selected to x
For Each ws In ThisWorkbook.Sheets
If Not IsError(Application.Match(ws.Name, SheetsToSearch, 0)) Then
With ws.Range("A1:AB5000")
Set r = .Find(what:=x, After:=.Range("A1")) 'find the cell whose value is equal to x and activate it
If Not r Is Nothing Then
ws.Activate: r.Activate
Exit Sub
End If
End With
End If
Next
End Sub
Display More