User-Defined Functions

css navigation by Css3Menu.com

Concatenate Cells

Some predecessor of mine had built an unbelievably long CONCATENTATE statement with multiple IFs. My missoin was to add 3 more elements to the statement. Here is what I started with:
=IF(D6="","Blank",CONCATENATE($F6," Oz ",$C6,IF($D6="S"," Soft",
          " Hard"),(" "),($E6),IF($J6="",""," "&$J6)))
And this is what I had before deciding to write a custom function to replace this tedious conglomeration.
=IF(D15="","Blank",CONCATENATE(F15," Oz ",C15,IF(D15="S"," Soft ",
         " Hard "),E15," ",G15," ",H15," ",I15," ",J15))

 Public Function ConcatName(theRow As Long)
   '+------------------------------------------------------
   '|  Created 10/03/2006  Alan Barasch    
   '+------------------------------------------------------
    Dim AdditAttrib As String
    '=IF(D6="","Blank",CONCATENATE($F6," Oz " _
,$C6,IF($D6="S"," Soft"," Hard"),(" "),($E6),IF($J6="",""," "&$J6)))
    '=IF(D15="","Blank",CONCATENATE(F15," oz ",C15,IF(D15="S", _
	" Soft "," Hard "),E15," ",G15," ",H15," ",I15," ",J15))
Application.MacroOptions _
    Macro:="ConcatName", _
    Description:="Use ConcatName(ROW()) in B column"
    ConcatName = ActiveCell.Row
    If Cells(theRow, 4) = "" Then
        ConcatName = "Blank"
    Else
        AdditAttrib = Cells(theRow, 5) & Chr(32) & Cells(theRow, 7) & _
            Chr(32) & Cells(theRow, 8) & Chr(32) & Cells(theRow, 9)    
                   'Get additional parts
        If Cells(theRow, 4) = "S" Then    'Soft or Hard
            If Cells(theRow, 10) = "" Then
                ConcatName = Cells(theRow, 6) & " Oz " & Cells(theRow, 3) _
                    & " Soft " & Chr(32) & AdditAttrib
            Else
                ConcatName = Cells(theRow, 6) & " Oz " & Cells(theRow, 3) _
                    & " Soft " & Chr(32) & Cells(theRow, 10) & AdditAttrib
            End If
        Else
            If Cells(theRow, 10) = "" Then
                ConcatName = Cells(theRow, 6) & " Oz " & Cells(theRow, 3) _
                    & " Hard " & Chr(32) & AdditAttrib   
                   'If shape is included
            Else
                ConcatName = Cells(theRow, 6) & " Oz " & Cells(theRow, 3) _
                    & " Hard " & Chr(32) & Cells(theRow, 10)
            End If
        End If
    End If
    AdditAttrib = ""
    theRow = 0
End Function

The syntax for using the user-defined function is -ConcatName(ROW()).

© 2006-2024

Updated:  06/21/2024 07:42
This page added:  03 October 2006