samedi 17 avril 2021

VBA - How to tell 13 combinatios by randomize to remain under 100?

i would like to randomize 13 variables which in sum have to be 100%

for a better understanding i show you what i mean by a snippet. From column F to column R all variables have to understand that in sum (column S) these are not allowed to be over 100.

Each row shall be another scenario, so the rows by itself are independent. enter image description here

my approach was like the following but unfortunately nothing happens. Anyone an idea? Thanks a lot.

Sub Zufall()
    Application.ScreenUpdating = False
    Range("F11:R55").ClearContents
    DoEvents
    Do Until WorksheetFunction.Sum(Range("S11:S55")) = 100
    Randomize
    For k = 19 To 3 Step -1
    Cells(11, k).Formula = Int(Rnd * 100)
    Next k
    Range("F11:R55").Value = WorksheetFunction.Sum(Range("S11:S55"))
    DoEvents
    Loop
    Application.ScreenUpdating = True
    End Sub


 



Aucun commentaire:

Enregistrer un commentaire