VBA for Stock Allocation

[Excelboo2]

Let say I have 5 shops: A, B, C, D, and E
They request for stock replenishment from warehouse as follows:
A: 4 pieces
B:2 pieces
C:3 pieces
D:3 piece
E:3 piece

But since the warehouse only have 12 pieces of this product so need to allocate to the shops in rounds that is:
Round 1: one piece is allocated to each shops (Stock left=12-5=7)
Round 2: one piece is allocated to each shops (Stock left=7-5=2)
Round 3: one piece each is allocated to A and C (the priority should be B but since B just requested for 2 pieces only which has been fulfilled) since there are no more stock

My question is how to write the scripts to do this allocation. Thanks

 

[DonkeyOte]

can you clarify the priority rule -- you say B should be priority -- is that because B requested least pieces or because there is some preferential ordering based on store ?

 

[al_b_cnu]

Hi,

The request is fairly straightforward, but any code would need to be tied in with the s/sheet layout.
I suspect ythat your requirement is somewhat more complicated than you stated, maybe somethin like 2 worksheets, the first holding shop data something like:

Book1
	A	B	C	D	E
1	Shop	Product	Current Stock	Requested	Allocated
2	A	A1	3	4
3	A	A2	11
4	A	A3	19
5	A	A4	6
6	A	A5	8
7	B	A1	6	2
8	B	A2	8
9	B	A3	0
10	B	A4	3
11	B	A5	8
12	C	A1	3	3
13	C	A2	5
14	C	A3	9
15	C	A4	12
16	C	A5	12
17	D	A1	8	3
18	D	A2	5
19	D	A3	10
20	D	A4	15
21	D	A5	2
22	E	A1	7	3
23	E	A2	8
24	E	A3	12
25	E	A4	1
26	E	A5	14
Shop Data

(continued...)

 

[al_b_cnu]

(... continued)

And the Warehouse Stock sheet looking something like:

Book1.xls
	A	B	C	D
1	Product	Available
2	A1	12
3	A2	7
4	A3	2
5	A4	1
6	A5	6
7	A6	6
8	A7	18
Warehouse Stock

Can you clarify your workbook layout?

 

[Excelboo2]

can you clarify the priority rule -- you say B should be priority -- is that because B requested least pieces or because there is some preferential ordering based on store ?

First priority: A,
2nd priority: B,
3rd priority: C
4th prority :D
5th priority: E

Thanks

 

[Excelboo2]

(... continued)


Can you clarify your workbook layout?

My sheet is very simple:

Column A: Product Code
Column B: Total Qty in Warehouse
Column C: Qty requested by ShopA
Column D: Qty requested by Shop B
.
.
.

 

[al_b_cnu]

My sheet is very simple:

Column A: Product Code
Column B: Total Qty in Warehouse
Column C: Qty requested by ShopA
Column D: Qty requested by Shop B
.
.
.

Yep, seems fairly straightforward enough. Where would you want the allocations output to be placed?

 

[Excelboo2]

Yep, seems fairly straightforward enough. Where would you want the allocations output to be placed?

column I for shopA allocation result
Column J for Shop B..........
.
.
.

 

[al_b_cnu]

Hi,

first cut:

Option Explicit

Sub AllocateStock()
'-- Constants --
Const iNumberOfShops As Integer = 5
Const iTotalWhouseStockColumn As Integer = 2 'column B = totoal Warehouse Stock column
Const iShop1RequestColumn As Integer = 3    'column C = 1st Shop Stock Request column
Const iShop1AllocationColumn As Integer = 9 'Column I = 1st shop Stock allocation Column
Const sWorksheetName As String = "Sheet1"

'-- Variable Declarations --
Dim iCol As Integer
Dim lRow As Long, lRowEnd As Long
Dim lInitialAllocation As Long, lAdditionalAllocation As Long
Dim lRemainder As Long
Dim lTotalWarehouseStock As Long
Dim rCur As Range
Dim sCurRange As String
Dim vaRequests() As Variant, vaAllocations() As Variant
Dim WS As Worksheet

Set WS = Sheets(sWorksheetName)

lRowEnd = WS.Cells(Rows.Count, "A").End(xlUp).Row

ReDim vaRequests(1 To 1, 1 To iNumberOfShops)
ReDim vaAllocations(1 To 1, 1 To iNumberOfShops)

For lRow = 2 To lRowEnd
    lTotalWarehouseStock = Val(WS.Cells(lRow, iTotalWhouseStockColumn).Value)
    Set rCur = WS.Range(Cells(lRow, iShop1RequestColumn).Address, Cells(lRow, iShop1RequestColumn + iNumberOfShops - 1).Address)
    vaRequests = rCur.Value
    lInitialAllocation = Int(lTotalWarehouseStock / iNumberOfShops)
    lRemainder = lTotalWarehouseStock Mod iNumberOfShops
    For iCol = 1 To iNumberOfShops
        lAdditionalAllocation = 0
        If lRemainder > 0 Then
            If Val(vaRequests(1, iCol)) > lInitialAllocation Then
                lAdditionalAllocation = 1
            End If
        End If
        vaAllocations(1, iCol) = lInitialAllocation + lAdditionalAllocation
        lRemainder = lRemainder - lAdditionalAllocation
    Next iCol
    
    Set rCur = WS.Range(Cells(lRow, iShop1AllocationColumn).Address, Cells(lRow, iShop1AllocationColumn + iNumberOfShops - 1).Address)
    rCur.Value = vaAllocations
Next lRow
End Sub

 

[Excelboo2]

Checking it out, thanks guy