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?
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?