Need to modify a User Defined Function

[daymaker]

I'm a newbie with VBA. I've tried writing a user defined function :

Public Function SplitNames(NamesCell As Range)
    Dim vecNames As Variant
    Dim cell As Variant
    Dim yourPcge As String
    Dim tmp As String
     
    vecNames = Split(NamesCell.Value, ",")
    For Each cell In vecNames
         
        yourPcge = "=IF(A5="","",IF(SUMPRODUCT(--($A$2:$A5&$F$2:$F5=A5&F5))=1,(20-H5)/20,(20-SUMIF($F$2:$F5,F5,$H$2:$H5))/20))"
        tmp = tmp & cell & " : " & yourPcge & " ; "
    Next cell
     
    SplitNames = Left$(tmp, Len(tmp) - 3)
End Function

Now in the line,

yourPcge = "=IF(A5="","",IF(SUMPRODUCT(--($A$2:$A5&$F$2:$F5=A5&F5))=1,(20-H5)/20,(20-SUMIF($F$2:$F5,F5,$H$2:$H5))/20))"
        tmp = tmp & cell & " : " & yourPcge & " ; "

The formula is actually an excel formula. How do I change it so that it fits into the user defined function and gives me the expected result?

[Bob Phillips]

You don't give us much to go on, but perhaps it should be

[vba]

Public Function SplitNames(NamesCell As Range)
Dim vecNames As Variant
Dim cell As Variant
Dim yourPcge As String
Dim tmp As String

vecNames = Split(NamesCell.Value, ",")
For Each cell In vecNames

yourPcge = "=IF(A5="","",IF(SUMPRODUCT(--($A$2:$A5&$F$2:$F5=A5&F5))=1,(20-H5)/20,(20-SUMIF($F$2:$F5,F5,$H$2:$H5))/20))"
tmp = tmp & cell & " : " & Application.Evaluate(yourPcge) & " ; "
Next cell

SplitNames = Left$(tmp, Len(tmp) - 3)
End Function[/vba]