dimanche 18 avril 2021

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

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?

enter image description here

    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



Aucun commentaire:

Enregistrer un commentaire