Both of the following assume you have a header row and that you want to loop through allw sheets in the workbook.
This version allows you to hard code the text into the array:
Sub findAll2()
Dim i As Long, j As Long, wList As Variant, curSht As Worksheet, x As Integer
Dim sh As Integer
wList = Array("a", "b", "c") 'change to your search list
For sh = 1 To Worksheets.Count
Set curSht = Worksheets(sh)
j = 2
Do Until curSht.Cells(j, 1) = ""
On Error Resume Next
If Not Application.WorksheetFunction.Match(curSht.Cells(j, 1), wList, 0) = xlErrNA Then
If Err.Number <> 1004 Then
curSht.Cells(j, 2) = "Check"
End If
End If
j = j + 1
Loop
Next
End Sub
This version takes a list from column H and creates a range from the list to be used in the search:
Option Explicit
Sub findAll()
Dim i As Long, j As Long, wList As Range, curSht As Worksheet, x As Integer
Dim sh As Integer
i = 2
Do Until Cells(i, 8) = ""
If x = 0 Then
Set wList = Cells(i, 8)
x = 1
Else
Set wList = Union(wList, Cells(i, 8))
End If
i = i + 1
Loop
For sh = 1 To Worksheets.Count
Set curSht = Worksheets(sh)
j = 2
Do Until curSht.Cells(j, 1) = ""
On Error Resume Next
If Not Application.WorksheetFunction.Match(curSht.Cells(j, 1), wList, 0) = xlErrNA Then
If Err.Number <> 1004 Then
curSht.Cells(j, 2) = "Check"
End If
End If
j = j + 1
Loop
Next
End Sub
Bookmarks