Thread: UDF for many if statements

Hi,
I have a database which I need to simplify into the following category groups:

• Triple
• Suncool
• Fire
• Lam
• Activ
• Planar
• K
• K S
• Other

However this would involve nesting the following IF formulas. In the past it was suggested I use a UDF, can anyone help with this based on the following rules please:
1. If V2 contains any data then “Triple”
2. If R2 or T2 or V2 contains *Suncool* or *Sun* then “Suncool”
3. If R2 or T2 or V2 contains *pyrodur* or *Pyrostop*, then “Fire”
4. If R2 or T2 or V2 contains *lam* or *phon* then “Lam”
5. If R2 or T2 or V2 contains *Activ*, then “Activ”
6. If R2 or T2 or V2 contains *Planar*, then “Planar”
7. The tricky one is this one.. If any product codes in R2,T2, V2 are contained in the data table on the tab “product group” then apply the formula:

=IF(COUNTIF('Product Group'!$M$5:$Q$8,Q2),INDEX('Product Group'!$M$5:$M$8,SUMPRODUCT(('Product Group'!$M$5:$Q$8=Q2)*(ROW('Product Group'!$M$5:$Q$8)-ROW('Product Group'!$M$5)+1))),AO2&" Other")

However the above formula only looks in Q2 – this needs to be changed to look into R2,T2 and V2

This will assign the product groups K and K S

8. If none of the above apply then “Other”

I have attached a sample file – in the actual file there are over 40000 entries to apply this to.

When I used a UDF before for “Planar” for example I was given this code:

If rRng.Value Like "*Planar*" Then
    fTMS = "Planar"
    Exit Function
End If

However I need it to look within 3 cells rather than one.

The formula would need to go in column AL

Any help is greatly appreciated, even if you can help with only one part

Paul

Public Function DBValue( _
    ByVal Q2 As Range, _
    ByVal R2 As Range, _
    ByVal T2 As Range, _
    ByVal V2 As Range, _
    ByVal AO2 As Range, _
    ByVal ProductGroup As Range) As String
Const FORMULA_MATCH_INDEX As String = _
    "SumProduct((<products>=<Q2>)*" & _
    "(Row(<products>)-Row(<productstart>)+1)))"
Dim idxValue As Long

    vecSun = Array()
    If V2.Value2 <> "" Then
        DBValue = "Triple"
    ElseIf R2.Value Like "*Sun*" Or T2.Value Like "*Sun*" Or V2.Value Like "*Sun*" Then
        DBValue = "Suncool"
    ElseIf (R2.Value Like "*pyrodur*" Or R2.Value Like "*Pyrostop*") Or _
        (T2.Value Like "*pyrodur*" Or T2.Value Like "*Pyrostop*") Or _
        (V2.Value Like "*pyrodur*" Or V2.Value Like "*Pyrostop*") Then
    ElseIf (R2.Value Like "*lam*" Or R2.Value Like "*phon*") Or _
        (T2.Value Like "*lam*" Or T2.Value Like "*phon*") Or _
        (V2.Value Like "*lam*" Or V2.Value Like "*phon*") Then
        DBValue = "Lam"
    ElseIf R2.Value Like "*Activ*" Or T2.Value Like "*Activ*" Or V2.Value Like "*Activ*" Then
        DBValue = "Activ"
    ElseIf R2.Value Like "*Planar*" Or T2.Value Like "*Planar*" Or V2.Value Like "*Planar*" Then
        DBValue = "Planar"
    ElseIf (IIf(R2.Value <> "", Application.CountIf(ProductGroup, Left$(R2.Value, 6)), 0)) + _
        (IIf(T2.Value <> "", Application.CountIf(ProductGroup, Left$(R2.Value, 6)), 0)) + _
        (IIf(V2.Value <> "", Application.CountIf(ProductGroup, Left$(V2.Value, 6)), 0)) > 0 Then
        
        If Application.CountIf(ProductGroup, Q2.Value) Then
        
            idxValue = Application.Evaluate(Replace(Replace(Replace(FORMULA_MATCH_INDEX, _
                                                            "<Q2>", Q2.Value), _
                                                    "<productstart>", ProductGroup.Cells(1, 1).Address), _
                                            "<products>", ProductGroup.Address))
            DBValue = Application.Index(ProductGroup.Columns(1), idxValue)
        Else
        
            DBValue = AO2.Value & " Other"
        End If
    End If
End Function

Hi Bob

Thank you for replying to me and providing the code.

I am just about to test this but fell at the first hurdle - how do I ativate the code? I inserted the formula: =DBValue() but I have a text box that comes up asking me for entries in Q2, R2, T2, V2, AO2 and Product group - what do I input in these? Attached Picture

for this part:

ElseIf (R2.Value Like "*pyrodur*" Or R2.Value Like "*Pyrostop*") Or _
        (T2.Value Like "*pyrodur*" Or T2.Value Like "*Pyrostop*") Or _
        (V2.Value Like "*pyrodur*" Or V2.Value Like "*Pyrostop*") Then

would I need to add in?:

HTML Code: 

DBValue = "Fire"

Anyone know how i can get this to work?

Originally Posted by OnErrorGoto0

Bob did try and make the parameters fairly self-evident - something like

=DBValue(Q2,R2,T2,V2,AO2,'Product Group'!$M$5:$Q$8)

I even tried making the parameters self-documenting to help

Hi,

That simple but yet I still couldnt figure it out!

I have started testing this and I have a problem with it not picking up products which appear in the product group table, this refers to this code:

ElseIf (IIf(R2.Value <> "", Application.CountIf(ProductGroup, Left$(R2.Value, 6)), 0)) + _
        (IIf(T2.Value <> "", Application.CountIf(ProductGroup, Left$(R2.Value, 6)), 0)) + _
        (IIf(V2.Value <> "", Application.CountIf(ProductGroup, Left$(V2.Value, 6)), 0)) > 0 Then
        
        If Application.CountIf(ProductGroup, Q2.Value) Then
        
            idxValue = Application.Evaluate(Replace(Replace(Replace(FORMULA_MATCH_INDEX, _
                                                            "<Q2>", Q2.Value), _
                                                    "<productstart>", ProductGroup.Cells(1, 1).Address), _
                                            "<products>", ProductGroup.Address))
            DBValue = Application.Index(ProductGroup.Columns(1), idxValue)
        Else
        
            DBValue = AO2.Value & " Other"

Can anyone see why this isnt working?

I have uploaded the file using the code supplied in column AL, the highlighted red entries are the cells which are not working correctly.

Regards

Paul

Hi,

What this part of the formula would need to do is look in cells Q2, S2 and U2 and if any of the products appear within the product table on "product group" tab then return the category in column M.
e.g. on the previously attached file in cell Q3, this appears on the product group tab in cell N5 so "4mm K" should be returned.

If it doesn’t appear on the product group tab then cell AO2 and “Other” should be returned

I can see the formula is looking in columns R, T, V which is probably my fault in explaining it previously. I am sure it isnt as simple as changing R2 to Q2, T2 to S2 and V2 to U2.

Can anyone help with the amendment?

Yes, most of the formula works - it is only when it comes down to this part which is incorrect. All code above this can be left the same as it is working.

The statement below by me is wrong:
"If any product codes in R2,T2, V2 are contained in the data table on the tab “product group” then apply the formula..."

This should be Q2, S2 and U2

ill test this now!!

Hi,

Still testing this but found something that I need including if possible:
If cell M2 contains "150" Then "Toughened"
If cell M2 contains "130" or or "210" or "999" Then "Misc"
If cell M2 contains "800" then "Carriage"


I would need this to go near the top of the code before tripple if possible. Can you help?