Сравнение значений ячеек в Excel

Sub reshen()
    Dim i As Integer
    Dim value As String
    Dim Celevye As Variant
    Dim TotalRows As Long
    Dim N As Integer
    TotalRows = Rows(Rows.Count).End(xlUp).Row
    Celevye = Array("Труба", "Гвоздь")
    N = 500
    Z = 50
            For j = 1 To Z
                For i = 1 To N
                    If InStr(1, Cells(i, 2), "Труба") <> 0 Then
                    Cells(i, 3) = "Труба"
                End If
                Next i
            Next j
End Sub

Private Sub Test()
    Dim a1, a2, t, i1&, i2&
    a1 = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Value
    a2 = Range("F1:F3").Value
    For i1 = 1 To UBound(a1)
        t = a1(i1, 1): a1(i1, 1) = Empty
        For i2 = 1 To 3
            If InStr(1, t, a2(i2, 1), vbTextCompare) = 1 Then
               a1(i1, 1) = a2(i2, 1)
               Exit For
            End If
        Next
    Next
    Range("C1").Resize(i1 - 1) = a1
End Sub

Private Sub Test2v1()
    Dim a1, a2, t, i&
    a1 = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Value
    a2 = Range("F1:F3").Value
    For i = 1 To UBound(a1)
        t = Split(a1(i, 1))(0): a1(i, 1) = Empty
        If Not IsError( _
        Application.Match(t, a2, 0)) Then a1(i, 1) = t
    Next
    Range("C1").Resize(i - 1) = a1
End Sub

Private Sub Test2v2()
    Dim r As Range, a, t, i&
    a = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Value
    Set r = Range("F1:F3")
    For i = 1 To UBound(a)
        t = Split(a(i, 1))(0): a(i, 1) = Empty
        If Application.CountIf(r, t) > 0 Then a(i, 1) = t
    Next
    Range("C1").Resize(i - 1) = a
End Sub

t = Split(a(i, 1))(0): a(i, 1) = Empty
If Application.CountIf(r, t) > 0 Then a(i, 1) = t
Range("C1").Resize(i - 1) = a