Change my If statement to use an Array instead-CodePudding

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

CodePudding user response：

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",""))