Originally Posted by

**hshone**
Hi all,

I have a list of data:

Apple 580

Pear 245

Orange 867

Cherry 870

etc.

What I want to do is split my inventory between 5 locations randomly, but will only ever total the total inventory and then have VBA print the array so that it's like the following:

Apple 156

Apple 142

Apple 52

Apple 68

Apple 162

etc.

I have done this in excel, but very manual generating 5 random numbers then divide the random number by the Sum of 5 random numbers so that I get 100% the multiplying each % by the total number of apples and so on.

Excel Formula:

=(RANDOM NUMBER/SUM(RANDOM NUMBERS 1-5))*Inventory

To avoid all the decimals I'm rounding the numbers and row 5 is the total inventory - sum of 4 so that I wont have +-1 either side due to decimals

I have the following snippet to make the random numbers :

Anybody got an hints how I should approach this, I would presume some kind of array with loops i.e. row 1 calculate row 5 inventory then row 2 etc. and then print all the values to a new sheet.

Not sure if this is right..but it should be a good start

Code:

Sub Test()
Dim invArray(3, 6)
invArray(0, 0) = "Apple": invArray(0, 1) = 580
invArray(1, 0) = "Pear": invArray(1, 1) = 245
invArray(2, 0) = "Orange": invArray(2, 1) = 867
invArray(3, 0) = "Cherry": invArray(3, 1) = 870
For x = LBound(invArray, 1) To UBound(invArray, 1)
curInv = invArray(x, 1)
For y = LBound(invArray, 2) + 2 To UBound(invArray, 2)
Randomize Timer
invArray(x, y) = CInt(Rnd * ((curInv - 1)) * 0.75) + 1
curInv = curInv - invArray(x, y)
If y = UBound(invArray, 2) Then
invArray(x, y) = invArray(x, y) + curInv
End If
Next y
Next x
Range(Cells(1, 1), Cells(UBound(invArray, 1) + 1, UBound(invArray, 2) + 1)) = invArray
End Sub

## Like this thread? Share it with others