PDA

View Full Version : Need to modify a User Defined Function



daymaker
08-05-2011, 01:27 AM
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
08-05-2011, 07:25 AM
You don't give us much to go on, but perhaps it should be



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