Pallets will have a certain size. So they may have a maximum Area (sq ft) or maximum volume (cu ft) representing the Capacity of a pallet. In some cases there may be a weight limit for a pallet, but that doesn't appear to apply to your question/set up.
Where does PalletQty come from? That's the Capacity I mentioned above.
Total number of Items = 380
PalletCapacity= 25
Required number of pallets = iif(380 mod 25 = 0, 389/25,380\25 +1)
' if 380 (Qty) is divisible by 15 (Capacity) evenly (that is 0 remainder) then 380/15
'if it is not evenly divisible, then integer divide Qty\Capacity +1. The +1 is to handle the partial pallet.
So your general formula would be
PalletsRequired =iif (TotalQty mod Capacity=0,TotalQty/Capacity, TotalQty\Capacity +1)
You could make this a function:
Code:
' ----------------------------------------------------------------
' Procedure Name: PalletsRequired
' Purpose: to determine the number of pallets required for a shipment
' Procedure Kind: Function
' Procedure Access: Public
' Parameter lTotalQuantity (Long): Total quantity of Items to Ship
' Parameter lCapacity (Long): Pallet capacity
' Return Type: Integer
' Author: Jack
' Date: 02-Jun-20
' ----------------------------------------------------------------
Function PalletsRequired(lTotalQuantity As Long, lCapacity As Long) As Integer
On Error GoTo PalletsRequired_Error
If lTotalQuantity > 0 And lCapacity > 0 Then
PalletsRequired = IIf(lTotalQuantity Mod lCapacity = 0, lTotalQuantity / lCapacity, lTotalQuantity \ lCapacity + 1)
Else
PalletsRequired = 9999 'to indicate an error in supplied parameters
End If
On Error GoTo 0
PalletsRequired_Exit:
Exit Function
PalletsRequired_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PalletsRequired, line " & Erl & "."
GoTo PalletsRequired_Exit
End Function
sample usage:
?palletsrequired(380,-8)
9999
?palletsrequired(380,15)
26
Good luck.