If statement check

**Royalmathmtcs** · 12-03-2008, 12:35 PM

I would like this code to check if there is already something in the cell, and if so, add a comma and space, then output the next appropriate condition of the if statement. The way I have the code written currently, it checks one column to see if a word exists, then if so, it outputs a word associated with this condition. Under this method, the code does not take account for columns that have more than one matching word. I've included a small portion of the code, there are more elseif statements, but they are redundant for this purpose. Thanks in advance for your help!!!

Sub Industry()

'Change this to reflect your true starting row.
vStartRow = 8

For i = vStartRow To Cells(Rows.Count, "M").End(xlUp).Row

'ITE
    If InStr(1, Range("M" & i).Value, "UL 60950") Or InStr(1, Range("M" & i).Value, "UL 60950-1") _
        Or InStr(1, Range("M" & i).Value, "UL 1459") Or InStr(1, Range("M" & i).Value, "UL 497") _
        Or InStr(1, Range("M" & i).Value, "UL 497A") Or InStr(1, Range("M" & i).Value, "UL 497B") _
        Or InStr(1, Range("M" & i).Value, "UL 497C") Or InStr(1, Range("M" & i).Value, "UL 1863") _
        Or InStr(1, Range("M" & i).Value, "UL 1310") Or InStr(1, Range("M" & i).Value, "UL 1012") _
        Or InStr(1, Range("M" & i).Value, "UL 61965") Or InStr(1, Range("M" & i).Value, "UL 60601-1") _
        Or InStr(1, Range("M" & i).Value, "UL 60601A-1") Or InStr(1, Range("M" & i).Value, "UL 60601B-1") _
        Or InStr(1, Range("M" & i).Value, "UL 60601C-1") Or InStr(1, Range("M" & i).Value, "UL 1863") Then
            Range("N" & i).Value = "ITE"

'Insulating Materials
    ElseIf InStr(1, Range("M" & i).Value, "UL 94") Or InStr(1, Range("M" & i).Value, "UL 840") _
        Or InStr(1, Range("M" & i).Value, "UL 1446") Or InStr(1, Range("M" & i).Value, "UL 2353") _
        Or InStr(1, Range("M" & i).Value, "UL 1692") Or InStr(1, Range("M" & i).Value, "UL 224") _
        Or InStr(1, Range("M" & i).Value, "UL 1441") Or InStr(1, Range("M" & i).Value, "UL 1696") _
        Or InStr(1, Range("M" & i).Value, "UL 969") Then
            Range("N" & i).Value = "Insulating Materials"

End If
Next i

End Sub

**Leith Ross** · 12-03-2008, 12:42 PM

Hello Royalmathmtcs,

Welcome to the Forum!

To get an accurate and quick solution to your problem, it would help to know how the data is laid out in the workbook. The easiest way to do this is to post your workbook for review. If you are using Excel 2007 then you should also post a copy in Excel 2003 format also.

Sincerely,
Leith Ross

**Royalmathmtcs** · 12-03-2008, 12:56 PM

Thanks for the reply Leith, I attached an example.

**Leith Ross** · 12-03-2008, 01:02 PM

Hello Royalmathmtcs,

Will you be adding to the list in the future?

Sincerely,
Leith Ross

**Krishnakumar** · 12-03-2008, 01:16 PM

Hi,

Here is a sample. Work on this.

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") 'add all those values. create arrays like this
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) 'add like this
        y = Application.Match(Trim(x(c)), applAry, 0)
        If Not IsError(y) Then w(i, 1) = "Appliances": Exit For
    Next
Next
With Range("n8")
    .Resize(i - 1).Value = w
End With
End Sub

HTH

**Royalmathmtcs** · 12-03-2008, 01:56 PM

That looks like it might work. The sample you have provided outputs everything to the same column it is gathering the information from to check within the array, erasing data that needs to stay in that column. How do I move the output from this code into column N, starting at the same row as column M?

**Royalmathmtcs** · 12-03-2008, 02:06 PM

Nevermind about finding the correct column to output to, time to see if this works for the entire workbook!

**Royalmathmtcs** · 12-03-2008, 02:35 PM

I added a few more arrays and have the code working, but it doesnt add a comma to the end of the previous entry in the new column. It is overwriting anything in the column that should be kept. For example, if there is a scenerio where there is more than one match is found in the column the array is checking, it will output the match from the array further down in the code, rather than placing a comma behind the first entry and then adding the second match. What needs to be modified?

**Leith Ross** · 12-03-2008, 03:07 PM

Hello Royalmathmtcs,

I have found you have duplicate entries for the following types:

UL 1863 Twice in ITE
UL 1450 Twice in Appliances
UL 1678 Twice in Consumer Electronics

These occur in both HVAC and Insulating Materials

UL 2353
UL 1692
UL 224
UL 1441
UL 1696
UL 969

What do you want to do with these?

Sincerely,
Leith Ross

**Royalmathmtcs** · 12-03-2008, 01:12 PM

I posted an small example of what I have, as there are about 1000 entries. To answer your question, no I will not have to add any more entries to this list.

**Royalmathmtcs** · 12-04-2008, 02:47 PM

Krishnakumar, I really appreciate your help!

I changed that segment that you posted, and now the code properly implements the comma that was missing before. I added more arrays and For Next statements and now I am running into the problem of not getting the correct output from the for next statement. I think this is because the way that you showed me only cosiders a scenerio with two arrays.

An example would be for UL 489. When the code looks for this it should see that its in the power distributions array, and output power distributions in the N column. Instead, the code is outputing ITE, Consumer Electronics, Power Distribution, Security & Signal in the N column. In one case I found with multiple entries in the M column, it didn't even output Power Distribution when UL 489 was present. Here is the code that I have now. I cut out most of the array section, as that has been entered correctly. What do I need to do from here?

Sub kTest()
Dim a, i As Long, x, y, c As Long, iteAry, applAry, wirAry, conAry, insAry, firAry, comAry, hvaAry, iceAry, ligAry, _
medAry, gasAry, ppeAry, pfdAry, powAry, sigAry, watAry, hazAry

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

a = Range("n8", 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"
            Else
                w(i, 1) = "Appliances": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), wirAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Wire & Cable"
            Else
                w(i, 1) = "Wire & Cable": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), conAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Consumer Electronics"
            Else
                w(i, 1) = "Consumer Electronics": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), insAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Insulating Materials"
            Else
                w(i, 1) = "Insulating Materials": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), firAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Fire"
            Else
                w(i, 1) = "Fire": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), comAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Components"
            Else
                w(i, 1) = "Components": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), hvaAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "HVAC"
            Else
                w(i, 1) = "HVAC": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), iceAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "ICE"
            Else
                w(i, 1) = "ICE": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), ligAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Lighting"
            Else
                w(i, 1) = "Lighting": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), medAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Medical & Dental"
            Else
                w(i, 1) = "Medical & Dental": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), gasAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Gas & Oil"
            Else
                w(i, 1) = "Gas & Oil": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), ppeAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "PPE"
            Else
                w(i, 1) = "PPE": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), pfdAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "PFD"
            Else
                w(i, 1) = "PFD": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), powAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Power Distribution"
            Else
                w(i, 1) = "Power Distribution": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), sigAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Security & Signal"
            Else
                w(i, 1) = "Security & Signal": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), watAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Water"
            Else
                w(i, 1) = "Water": Exit For
                End If
            End If
    Next
    
    For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), hazAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
                w(i, 1) = w(i, 1) & ", " & "Hazardous Location"
            Else
                w(i, 1) = "Hazardous Location": Exit For
                End If
            End If
    Next
Next

With Range("n8")
    .Resize(i - 1).Value = w
End With
End Sub

**Royalmathmtcs** · 12-04-2008, 03:07 PM

Ok, I figured it out...The For Next statement for ITE needs to have the is else, ifempty check in it as well, not just for the sequential statements.

For c = 0 To UBound(x)
        y = Application.Match(Trim(x(c)), iteAry, 0)
        If Not IsError(y) Then
            If Not IsEmpty(w(i, 1)) Then
            w(i, 1) = w(i, 1) & ", " & "ITE"
        Else
            w(i, 1) = "ITE": Exit For
            End If
        End If
    Next

Thank you for the help, I couldn't have done this without you!!!!

**Leith Ross** · 12-04-2008, 03:34 PM

Hello Royalmathmtcs,

This version loads each industry type from its own array into a collection object. The macro uses the collection to recall the industry type name for the standard. No duplicates are listed in column "N". If any standard has not been entered, you will see #Value! displayed in the cell. Have look and let me know if you have any questions.

Sincerely,
Leith Ross

**Royalmathmtcs** · 12-04-2008, 06:39 PM

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 Ross** · 12-04-2008, 07:02 PM

Hello Royalmathmtcs,

You are correct about the macro. Since it is a UDF, it is called just like a standard worksheet formula. If you have more than 100 Standards per Industry name then you will need to increase the Keys array value. I was thinking that it might be good to load the standards from a worksheet. It would be easier to maintain and allow you to easily search for a standard, or sort them.

Sincerely,
Leith Ross

**Royalmathmtcs** · 12-04-2008, 07:00 PM

This is working very well now, is there any way to modify it so that #Value! only shows up when there isn't a single match in the standards column? The way it is right now, #Value! shows even when there is a matching standard available along with a standard that hasn't been defined yet.

If statement check

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If statement check

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions