View Full Version : Randomly distribution a number into a range of cells in VBA

09-07-2015, 03:32 AM
Hi all,
How to randomly distribute a known number into a range of cells using excel VBA. For example: if we have number (20) in A1, how to distribute this number (20) randomly in range (B1:B10), when you count the range(B1:B10) will equal to number (20).


09-07-2015, 09:59 AM
I don't understand what you mean by randomly distribute a value into a range.
With your example above, what might be the values in B1:B10?

09-07-2015, 10:04 AM
If we have number (20) in A1, how to distribute this number (20) randomly in range (B1:B10), when you Sum (Add) the values of range(B1:B10) will equal to (20).

09-07-2015, 01:28 PM
You could use this UDF.
Select B1:B10 and enter =RandomDistribution(A1) as an array formula, by pressing Ctrl-Shift-Enter (Cmd+Return for Mac) when entering the formula.

Function RandomDistribution(Number As Double, Optional IntegerResults As Boolean) As Variant
Dim Result As Variant
Dim rSize As Long, cSize As Long, tSum As Double, xSum As Double
Dim i As Long, j As Long

With Application.Caller
rSize = .Rows.Count
cSize = .Columns.Count
End With

ReDim Result(1 To rSize, 1 To cSize)

For i = 1 To rSize
For j = 1 To cSize
Result(i, j) = Rnd()
tSum = tSum + Result(i, j)
Next j
Next i

For i = 1 To rSize
For j = 1 To cSize
If IntegerResults Then
Result(i, j) = WorksheetFunction.Round(Result(i, j) * Number / tSum, 0)
Result(i, j) = Result(i, j) * Number / tSum
End If
xSum = xSum + Result(i, j)
Next j
Next i
Result(rSize, cSize) = Result(rSize, cSize) - (xSum - Number)

RandomDistribution = Result
End Function

09-08-2015, 05:00 AM
Thanks mikerickson,
I could not run the code, and I could not enter the formula as well. Would please make it more easy if you can.

Kenneth Hobs
09-08-2015, 05:54 AM
bbuk9999, Mike's code provided you with 2 solutions even though you did not detail which if either you wanted. It uses positive double numbers or with the 2nd option set to True, it provides positive integer numbers. Being random, the integers option may have a 0 and some duplicate values as well. That is the nature of random number for a small dataset.

Insert a Module into the Visual Basic Editor (VBE) and paste Mike's code.

The way that I used Mike's code was to select a range of cells like B1:B10, entered one of these forumulas, and pressed Shift+Control+Enter as Mike explained. That is about as simple as you can get.

09-08-2015, 07:04 AM
Can I run it by pressing the run button? If yes please let me know.

Kenneth Hobs
09-08-2015, 07:35 AM

If you don't have the Developer tab in your Ribbon, right click the Ribbon and customize it. Select the Developer tab. Once added, you can select Insert Object and insert your button control. I prefer Form controls rather than ActiveX these days. Right click the form button control and assign a macro like this one. It goes in a Module or the same one as the other code.

Sub RunMikesCode()
Range("B1:B10").FormulaArray = "=RandomDistribution(20)"
Range("B11").Formula = "=Sum(B1:B10)"
End Sub

09-08-2015, 08:23 AM
Thank both of you kenneth Hobs and mikerickson for your help.

09-08-2015, 08:57 AM
Hi kenneth Hobs and mikerickson
I need your help in the following issue:
In the attached VBA excel file, there are two sheets the first one which is 'Collection' and the second one which is 'Max_Collection'. The Collection sheet includes the code of the model, when you run it, you will see numbers, times, letters are generated. The second one includes the maximum collection number for each company with the container size and type.

What I am trying to achieve is to spread the maximum collection number in the 'Max_Collection' sheet for each company within weeks, days, and times that are generated in the 'Collection' sheet in each run.

In the 'collection' sheet the weeks are generated in columns (F, M, T), serial numbers are generated in columns (I, P, W), the times are generated in columns (J, Q, X), and the collection numbers are generated in columns (G, N, U).

I will be very grateful if anyone of you can assist me in this issue asap please.


09-09-2015, 03:54 AM
Dear kenneth Hobs and mikerickson,
Can anyone of you solve the problem?

Kenneth Hobs
09-09-2015, 05:19 AM
When cross-posting, please add the links where you posted the same as explained in: http://www.excelguru.ca/content.php?184

I can not see how your file relates to your first question. Maybe you should have started another thread? If it does relate, please workup a simple example. On one sheet, manually markup what you think a solution would look like.