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
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)
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
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()).
|