If statement check

Hi Leith,

This version looks a lot cleaner, and it appears that it will not enter duplicate industry names in the column, which is the only problem with the other code I was able to get working.

I believe that this is to be ran with =GetIndustryType()?

Also, if I were to add more keys with standard numbers, ie

Keys(7) = "UL 497C"

would I need to make this value higher?

Dim Keys(100) As String

Leith,

Is there a way to display: (error for any non existent standard) comma (industry for defined standard)? This way the user would be notified of the error, but still be able to see if there is an industry available for the company. If not, could you explain how to make is so that it does nothing for the error, and displays the industry for the standards that have been defined, even if they are in the same cell as a non defined standard?

Hi,

Sub kTest()
Dim a, i As Long, x, y, c As Long, iteAry, applAry
iteAry = Array("UL 60950", "UL 60950-1", "UL 1459", "UL 497", "UL 497A", "UL 497B", "UL 497C", _
            "UL 1863", "UL 1310", "UL 1012", "UL 61965", "UL 60601-1", "UL 60601A-1", "UL 60601B-1", _
            "UL 60601C-1", "UL 1863")

applAry = Array("UL 1286", "UL 962", "UL 1459")

a = Range("m8", Range("m" & Rows.Count).End(xlUp))
ReDim w(1 To UBound(a, 1), 1 To 1)

For i = 1 To UBound(a, 1)
    x = Split(a(i, 1), ",")
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), iteAry, 0)
        If Not IsError(y) Then w(i, 1) = "ITE": Exit For
    Next
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), applAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & "," & "Appliances"
                w(i, 1) = UNIQUE(w(i, 1))
                Exit For
            End If
        End If
    Next
Next
With Range("n8")
    .Resize(i - 1).Value = w
End With
End Sub
Function UNIQUE(v)
Dim x, e, i As Long
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    x = Split(v, ",")
    For i = 0 To UBound(x)
        If Not .exists(x(i)) Then
            .Add x(i), Nothing
        End If
    Next
    If .Count > 0 Then UNIQUE = Join$(.keys, ",")
End With
End Function

HTH