How to limit all variables in one row that the sum is 100%?

Question

does anyone has an idea how to command that the sum of each row is always 100%? Thant includes that the random variables within the row have to know the values of the cells in the same row or?

    Sub Randomm()
    Dim wf As WorksheetFunction, rng As Range, zum As Single, i As Integer
    Set wf = Application.WorksheetFunction
    Set rng = Range("F6:R50")
    Dim totalRow As Single
    
    rng.Formula = "=RAND()"
    rng.Value = rng.Value
    zum = wf.sum(rng)
    For i = 6 To 50
        Cells(i, 6).Value = Cells(i, 13).Value / zum
        
    Next i
    rng.NumberFormat = "0.00%"

    
    
    End Sub

    Function GenerateRandomNumbers(ByVal size As Integer) As Variant
    
    ReDim vals(1 To size) As Single
    Dim idx As Integer
    Dim sum As Single
    
    For idx = 1 To size
        vals(idx) = Rnd
        sum = sum + vals(idx)
    Next idx
    
    For idx = 1 To size
        vals(idx) = vals(idx) / sum
    Next idx
    
    GenerateRandomNumbers = vals
    
    End Function

Answer 1

Well, you could sample from Dirichlet distribution, which returns array of values summed to 1. Then you could scale it to 100% or to whatever value you need.

I have little knowledge about how VBA works, so this is just sketch of the code

Function GenerateRandomNumbers(ByVal size As Integer) As Variant

ReDim vals(1 To size) As Single
Dim idx As Integer
Dim sum As Single

For idx = 1 To size
    vals(idx) = -Log(1.0-Rnd) 'sample as exponential
    sum = sum + vals(idx)     'find total sum
Next idx

For idx = 1 To size
    vals(idx) = vals(idx) / sum 'normalization
Next idx

GenerateRandomNumbers = vals

End Function