Generate random number from 40-80 but the numbers generated should end in either 5 or 0

[Prabin Khanal]

Hi Guys,

Can you help me out as I need to generate 7 numbers between 40-80 which should total 450 but the number must end with either 5 or zero-

I am unable to find it so if somebody can help me that would be very nice.

Cheers
Prabin

 

[mrshl9898]

This is working reliably


Turn Iterations up. File > Options > Formulas > Maximum Iterations = 10,000

A1 =ROUNDDOWN(A2/5,0)*5
B1:G1 =ROUNDDOWN(RANDBETWEEN(40,80)/5,0)*5
H1 =SUM(A1:G1)

Then run:

Sub Macro1()

Dim x As Long


x = 1


Do Until x = 100
Application.StatusBar = x & " attempts out of 100"
If Range("A1") < 40 Or Range("A1") > 80 Or Range("H1") <> 450 Then
    Range("H1").GoalSeek Goal:=450, ChangingCell:=Range("A2")
End If


x = x + 1
Loop
    
End Sub

EDIT, looks to be working on the first try, maybe shorten the loop or remove.

Sub Macro1()

Dim x As Long


x = 1


Do Until x = 5
Application.StatusBar = x & " attempts out of 100"
If Range("A1") < 40 Or Range("A1") > 80 Or Range("H1") <> 450 Then
    Range("H1").GoalSeek Goal:=450, ChangingCell:=Range("A2")
End If


x = x + 1
Loop
    
End Sub

Sub Macro1()

    Range("H1").GoalSeek Goal:=450, ChangingCell:=Range("A2")

End Sub

 

[mrshl9898]

*Now not working on the first, either got very lucky there for a while or the first number wasn't truly random.

To rest you'd better clear A2

 

[steve the fish]

Heres one that seems to work fine:

Dim arr(0 To 6)

Do Until Application.Sum(arr) = 450
    For i = 0 To 6
        Randomize
        arr(i) = Int((16 - 8 + 1) * Rnd + 8) * 5
    Next
Loop

MsgBox Join(arr, ", ")