Distribute a value randomly across a range of cells

[lre712]

Hi
I have a value in cell B2 that I wish to distribute randomly across 7 cells.

B2 = 3000
B33:39 are the cells that I want 3000 randomly distributed to.

thanks
Lee

 

[AliGW]

In B33 to B39:

=B$2/ROWS(B$33:B$39)

 

[salim hasan]

Try please my_macro

Option Explicit

Sub salim_rand_table()
Dim i%, k%, x#, m%: m = 33
Dim My_val#
    If Not IsNumeric([b2]) Or [b2] = vbNullString Then
        My_val = 10000
        Else
        My_val = Int(Abs([b2]))
    End If
Dim myStart%: myStart = 1
Dim myEnd%: myEnd = 7
 With CreateObject("System.Collections.SortedList")
    For i = myStart To myEnd
       .Item(Rnd) = i
     Next i
   For k = 1 To 6
      Cells(m, 2) = Int((.GetByIndex(k) / 28) * My_val)
      x = x + Cells(m, 2)
      m = m + 1
   Next
   Cells(m, 2) = My_val - x
 End With
End Sub

 

[lre712]

Thanks for the great responses and help.

I have attached my workbook for your reference. What I would like to do now is:

Distribute the value from C40, to cells C:4:C18. The values can be spread randomly but must be less than the capacity in cells B4:B18 and total to the value in C40