Create udf that returns 1d array

[YasserKhalil]

Hello everyone

I am trying to create a UDF that returns a 1d array. The parameters of the UDF would be two numbers as two parameters and the output should be a 1d array of the result as following

Code: Select all

Function Distribute(ByVal n1 As Double, ByVal n2 As Double)

End Function

Example:
-----------
say the two numbers are 30 and 4
The calculation would be step by step 30/4 (=ROUNDUP(30/4,0)) > the result is [8] and the remaining would be 22 and the n2 would be 3
The second step is to divide 22/3 (=ROUNDUP(22/3,0)) > the result is [8] and the remaining would be 14 and the n2 would be 2
The third step is to divide 14/2 ((=ROUNDUP(14/2,0))) > the result is [7] and the remaining would be 7 and the n2 would be 1
if n1 equals 1 so this is the last step and the final result would be [7]

The final result would be a 1d array of the results across the steps: [8, 8, 7, 7]

Mr. Hans helped me before in similar issue

Code: Select all

Function Distribute(total As Long, num As Long)
    Dim i As Long
    ReDim amounts(1 To num) As Long
    For i = 1 To num
        amounts(i) = total \ num
    Next i
    amounts(num) = amounts(num) + total Mod num
    Distribute = amounts
End Function

But the results are 7,7,7,9

[YasserKhalil]

I have tried to do it with my poor approach

Code: Select all

Function Distribute(total As Long, num As Long)
    Dim i As Long, t As Double
    ReDim amounts(1 To num) As Long
    t = total
    For i = 1 To num
        If i = 1 Then t = total Else t = t - amounts(i - 1)
        amounts(i) = Application.WorksheetFunction.RoundUp(t / (num - i + 1), 0)
    Next i
    Distribute = amounts
End Function

Waiting for your review .. or a better approach

[HansV]

Code: Select all

Function Distribute(ByVal total As Long, ByVal num As Long)
    Dim i As Long
    ReDim amounts(1 To num) As Long
    For i = 1 To num
        amounts(i) = Application.RoundUp(total / num, 0)
        total = total - amounts(i)
        num = num - 1
    Next i
    Distribute = amounts
End Function

[YasserKhalil]

Thank you very much, my great tutor.
Best Regards