VBA for Stock Allocation

Excelboo2

Board Regular
Joined
Oct 11, 2006
Messages
175
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
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
ABCDE
1ShopProductCurrent StockRequestedAllocated
2AA134
3AA211
4AA319
5AA46
6AA58
7BA162
8BA28
9BA30
10BA43
11BA58
12CA133
13CA25
14CA39
15CA412
16CA512
17DA183
18DA25
19DA310
20DA415
21DA52
22EA173
23EA28
24EA312
25EA41
26EA514
Shop Data


(continued...)
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
(... continued)

And the Warehouse Stock sheet looking something like:
Book1.xls
ABCD
1ProductAvailable
2A112
3A27
4A32
5A41
6A56
7A66
8A718
Warehouse Stock


Can you clarify your workbook layout?
 

Excelboo2

Board Regular
Joined
Oct 11, 2006
Messages
175

ADVERTISEMENT

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

Board Regular
Joined
Oct 11, 2006
Messages
175
(... 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

Well-known Member
Joined
Jul 18, 2003
Messages
4,494

ADVERTISEMENT

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?
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi,

first cut:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,106
Messages
5,640,134
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top