Results 1 to 4 of 4
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    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>
    CurrentRow = ActiveCell.Address
    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
    AdditAttrib = ""
    TheRow = 0
    End Function
    </pre>

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    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)
    Dim AdditAttrib As String</pre>

    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •