How would I change this If statement to use an array Instead?
Dim with As Workbook: Set wb = ThisWorkbook
Dim sh As Worksheet: Set sh = wb.Worksheets("Sheet1")
Dim tbl As ListObject: Set table = ListObjects("Table1")
Dim lcount As Long: lounge = tbl.Range.Rows.Count
For x = 1 To lCount - 1
Sh.Cells(x + 1, 1).Value Like "*" & "Black" & "*" Then sh.Cells(x + 1, 2).Value = "Y"
Sh.Cells(x + 1, 1).Value Like "*" & "Yellow" & "*" Then sh.Cells(x + 1, 2).Value = "Y"
End sub
You can use this code:
'this ist the sub to show how to call the basic sub below
Public Sub test_selectColor()
Dim tblData As ListObject
Set tblData = ThisWorkbook.Worksheets(1).ListObjects("tblData") '---> adjust this to your needs
Dim arrCheckColors(1) As String
'adding * to use the like operator against it
arrCheckColors(0) = "*Black*"
arrCheckColors(1) = "*Yellow*"
selectColor tblData.DataBodyRange, arrCheckColors
End Sub
'this is the sub that does the work
Private Sub selectColor(rgData As Range, arrCheck() As String)
Dim arrData As Variant: arrData = rgData.Value 'read data to array
Dim iD As Long, iC As Long
For iD = LBound(arrData, 1) To UBound(arrData, 1)
For iC = LBound(arrCheck) To UBound(arrCheck)
If arrData(iD, 1) Like arrCheck(iC) Then
arrData(iD, 2) = "Y"
Exit For
End If
Next
Next
rgData.Value = arrData 'write array back to range
End Sub
But you could achieve the same by formula:
=LET(findColor,IFERROR(FIND(tblCheck[CheckColor],[@Color]),0), IF(SUM(findColor),"Y",""))
Ifstatement? What is the array?