Thread: UDF for many if statements

Public Function DBValue( _
       ByVal M2 As Range, _
       ByVal Q2 As Range, _
       ByVal R2 As Range, _
       ByVal S2 As Range, _
       ByVal T2 As Range, _
       ByVal U2 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
    Dim vaItem
    Dim vaMatch
    Dim bMatched As Boolean
    Dim sFormula As String
    vecSun = Array()

    If InStr(1, M2.Value, "150") > 0 Then
        DBValue = "Toughened"
    ElseIf InStr(1, M2.Value, "130") + InStr(1, M2.Value, "210") + InStr(1, M2.Value, "999") > 0 Then
        DBValue = "Misc"
    ElseIf InStr(1, M2.Value, "800") > 0 Then
        DBValue = "Carriage"
    ElseIf 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
        DBValue = "Fire"
    ElseIf R2.Value Like "*Activ*" Or T2.Value Like "*Activ*" Or V2.Value Like "*Activ*" Then
        DBValue = "Activ"
    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 "*Planar*" Or T2.Value Like "*Planar*" Or V2.Value Like "*Planar*" Then
        DBValue = "Planar"
    Else
        For Each vaItem In Array(Q2.Value, S2.Value, U2.Value)

            If Application.CountIf(ProductGroup, vaItem) Then
                bMatched = True
                sFormula = Replace(Replace(Replace(FORMULA_MATCH_INDEX, _
                                                                        "<Q2>", vaItem), _
                                                                        "<productstart>", ProductGroup.Cells(1, 1).Address), _
                                                                        "<products>", ProductGroup.Address)
                idxValue = Sheets("Product Group").Evaluate(sFormula)
                                                                        
                DBValue = Application.Index(ProductGroup.Columns(1), idxValue)
                Exit For
            End If
        Next vaItem

        If Not bMatched Then DBValue = AO2.Value & " Other"
    End If
End Function

You will need to adjust the formulas to add the M cell as the first argument.

Yes. (make sure you have the ranges in the right order)