1. User-Defined Function (2003/SP2)

I wrote a custom function to Concatenate a bunch of cells. Instead of having a long formula on each of 30 rows, I thought a function would be easier to maintain.

My dilemma is that I am having trouble with it recognizing the current row as the operator. I cannot make it re-calculate when I fill down.
<pre>Function ConcatName(CurrentRow As Range)
Dim TheRow As Long, AdditAttrib As String
<font color=red> '=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))</font color=red>
TheRow = 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
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
TheRow = 0
End Function
</pre>

I am sure I am missing something obvious. Anyone got suggestions??

2. Re: User-Defined Function (2003/SP2)

I'm confused - you pass CurrentRow as a Range argument, but you don't use it in the code. Instead, you set CurrentRow to a string value (ActiveCell.Address), then don't use that either.

How are you using this function in a cell formula?

3. Re: User-Defined Function (2003/SP2)

See! I am goofy!

My syntax is =ConcatName(A6) to tell it what row to use.

How about if I change to <pre>Function ConcatName(theRow As Long)

4. Re: User-Defined Function (2003/SP2)

I think that would work better. You can then call it using

=ConcatName(ROW())

The ROW function without arguments returns the number of the row containing the formula.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•