Get Random amounts depending on a cell value

[RAJESH SHAH]

Hello Experts
There are various amounts in column E starting from 0. The total of the amounts is displayed in the end. I want to replace the amounts with the help of a code button to get the total of amount mentioned in cell HI without missing any row and the amounts should be greater than 400 and less than 9950.
This is a sample data and the amount may change in cell H1 as per requirement. Amount can be anything in column H1 but is always less than the total amount in the end.
I am sharing the previous similar code of beyond excel for reference assuming that it is possible to do so. As there were no comments in the code I was not able to edit the code and do the needful.
I would appreciate if someone could help me with a code button to get the expected result ias displayed n column F. (not necessarily to get the same figures but the total should match).
Thank you in advance.

[Marc L]

According to your attachment a VBA demonstration as a beginner starter
(as a reminder all is explained in both Excel & VBA helps) :

Sub Demo1() 
    Dim L&, M%, N&(), R&, Z& 
        L = [E1].End(xlDown).Row 
        M = 9 
        ReDim N(2 To L, 0) 
    With Application 
    Do 
        For R = 2 To L:  N(R, 0) = .MRound(.RandBetween(450, 9900), 50):  Next 
        Z = .Sum(N) - [H1] 
     If Z > 0 Then 
     For R = L To 2 Step -1 
        If N(R, 0) - Z >= 450 Then 
            N(R, 0) = N(R, 0) - Z 
            Z = 0 
            Exit Do 
        ElseIf N(R, 0) > 4200 Then 
            Z = Z - N(R, 0) 
            N(R, 0) = .MRound(N(R, 0) / 10, 50) 
            Z = Z + N(R, 0) 
        End If 
     Next 
     End If 
        M = M - 1 
    Loop While M * Z 
    End With 
        If Z Then Beep Else Range("E2:E" & L) = N 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[RAJESH SHAH]

Marc L. Awesome. Thank you very much. Comments could have really helped me to edit and understand better. Anyways, will try and see if I can play with it. Thanks once again.

[Marc L]

First thanks for the rep' !

Again as all is explained in Excel & VBA respective help so you can understand it, the best way to at least start to learn !

Just note in some case if after the Max 9 attempts this demonstration can't find a matching result
so you will have to code the part for Z < 0 if only H1 and bounds can match with the number of rows …

[RAJESH SHAH]

Yes. I was facing an issue when I changed the amount in H1. Testing it with less and more rows and different amounts. I am hearing a beeping sound. The rows can range from 25 to 500 rows and the amount as I said in column H1 will be less than the sum total of column E. Still trying to understand the code ...

[RAJESH SHAH]

I understood this part does check the difference between the total sum and the value of H1. If the difference is >0 then the code will display the result.
Z = .Sum(N) - [H1]
If Z > 0 Then

[Marc L]

No it can't display the result as it must first try to reduce the sum (the issue when random can't be just random !)
then displays the result only when it succeeds …

[RAJESH SHAH]

It is perfect Marc. I have been doing this manually for years now. I have to change the amount in H1 as per my requirement which will be always less than the total sum of column E. In each scenario only the number of rows and the figure in H1 will be changed. It is as I expected. I really appreciate your time and effort for helping me with the code. Thanks man.

[Marc L]

So in this case if still H1 & bounds match the number of rows the result may be found with the first attempt …

[RAJESH SHAH]

Marc L. Understood clearly. Checked with different number of rows and different expected result amounts. It is perfect. The beep sound is a bonus. 💜

[RAJESH SHAH]

So in this case if still H1 & bounds match the number of rows the result may be found with the first attempt …

Yup. After the match too I can change the amounts by pressing the button as many times I want.

[bebo021999]

Alternative code:
Hope this approach is easy to understand for you.
This is how it works:
1- Random every rows first (regardless the total)
2- Check the sum (total after random), if sum>total then deduct 50 from random row
3- Repeat
Until the sum reaches the total.

Option Explicit 
Sub demo2() 
Dim i&, res(), r, num&, sum As Long, tota As Long 
num = Cells(Rows.Count, "A").End(xlUp).Row - 1 
tota = Range("H1").Value 
ReDim res(1 To num, 1 To 1) 
Randomize 
 
'First, genarate random value (from 450 to 9900) then save to array "res" 
For i = 1 To num 
    r = (Int(Rnd * 189) + 9) * 50 ' equals: randbetween(9,189) * 50 = randbetween(450,9900) 
    res(i, 1) = r ' generate r (=random from 450-9900)then save to result array, regardless to total in H1 
Next 
sum = WorksheetFunction.sum(res) ' this random sum could be equal or more than 890000 
 
'Second, deduct 50 from random row, repeat until the sum reachs 890000 
Do 
    r = Int(Rnd * num) + 1 ' equals randbetween(1,419) 
    If res(r, 1) > 450 Then res(r, 1) = res(r, 1) - 50 
    sum = WorksheetFunction.sum(res) 
Loop Until sum <= tota 
 
'afterall, paste the array back to the sheet 
Range("E2:E" & num + 1).Value = res 
End Sub  

[JohnTopley]

Whether or not is either macro works is random i.e. they can fail on an arbitrary set of values. Or loop "endlessly". Not an easy problem to solve!

[Marc L]

Hey John !

The reason why my demonstration is limited to nine attempts to avoid such NES (Never Ending Story) …

[RAJESH SHAH]

Thanks bebo021999. It is just amazing. You people are just great coders. Hopefully nothing is impossible with you guys as you can play and do anything with VBA excel if you are so so good at coding. I am so happy to be a part of this forum where I am getting full support and learning so many things, slowly but steadily.
Thanks for the comments too bebo021999.