# Thread: VBA List Data Thanks: 0 Likes: 0

1. ## VBA List Data

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 :

Code:
```Sub Number()

Dim RandomNumber1 As Integer
Dim RandomNumber2 As Integer
Dim RandomNumber3 As Integer
Dim RandomNumber4 As Integer

Randomize
LRandomNumber1 = Int((100 - 0 + 1) * Rnd + 0)
LRandomNumber2 = Int((100 - 0 + 1) * Rnd + 0)
LRandomNumber3 = Int((100 - 0 + 1) * Rnd + 0)
LRandomNumber4 = Int((100 - 0 + 1) * Rnd + 0)

End Sub```
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.  Reply With Quote

2. ## Re: VBA List Data 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```  Reply With Quote

3. ## Re: VBA List Data

Thanks;

This is what i have so far, but its a little clunky and keeps referring too/ recalc excel cells, Ideally want to keep the random numbers the same to speed it up a little hence using Integers with the random numbers.

Code:
```Dim h As Integer, i As Integer, j As Integer, k As Integer, last_row As Integer, MyArray() As Variant, l As Integer

last_row = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row

ReDim MyArray(5 * (last_row - 1), 2)

For h = 0 To last_row - 2
For i = 4 To 8
Worksheets(2).Cells(5, i).Value = Int((100 - 0 + 1) * Rnd + 0)
Next i

For j = 4 To 8
Worksheets(2).Cells(6, j).Value = Worksheets(2).Cells(5, j).Value / Application.WorksheetFunction.Sum(Worksheets(2).Range("D5:H5").Value)
Next j

For k = 4 To 7
Worksheets(2).Cells(4, k).Value = "=Round(" & Worksheets(2).Cells(6, k).Value & "*" & Worksheets(2).Cells(h + 2, 2).Value & ", 0)"
Next k

Worksheets(2).Cells(4, 8).Value = Application.WorksheetFunction.Round(Worksheets(2).Cells(h + 2, 2).Value - Application.WorksheetFunction.Sum(Worksheets(2).Range("D4:G4")), 0)

For l = 1 To 5
MyArray(l + (5 * h), 1) = Worksheets(2).Cells(h + 2, 1).Value
MyArray(l + (5 * h), 2) = Worksheets(2).Cells(4, l + 3).Value
Next l
Next h

Worksheets(2).Cells(10, 10).Resize(5 * (last_row - 1), 2).Value = MyArray```  Reply With Quote

4. ## Re: VBA List Data

Another option:-
Data in columns "A & B", Results in columns "D & E".
Code:
```Sub MG22Jul42
Dim Rng As Range, Dn As Range, n As Long
Dim c As Long, oSum As Long, R As Long, s As Long
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

For Each Dn In Rng
oSum = Dn.Offset(, 1).Value
s = 0
For n = 1 To 5
c = c + 1
Cells(c, 4) = Dn.Value
If n < 5 Then
R = Application.RandBetween(1, 50) / 100 * oSum
Else
R = Dn.Offset(, 1).Value - s
End If
Cells(c, 5) = R
s = s + R
oSum = oSum - R
Next n
Next Dn
End Sub```
Regards Mick  Reply With Quote

5. ## Re: VBA List Data

Did my code not work for you? Was there an error? I can likely fix it.  Reply With Quote

6. ## Re: VBA List Data Originally Posted by Steve_ Did my code not work for you? Was there an error? I can likely fix it.
Yes it did work, but spat the results over columns not rows.  Reply With Quote

## User Tag List

apple, inventory, numbers, rnd, total 