with VBA create a function that accepts arguments in an excel sheet

[ajudajoao]

Create an availability function that accepts the following arguments
○ Material code (cell A) - the material code to be analyzed
○ Location - a Warehouse Location String (cells H, I). It can be “Porto” or
"Lisbon"
○ Quantity - an integer with the quantity to order.
The function should return one of three possible results:
● “Available for immediate delivery” if the warehouse is located
have the quantity in stock.
● “Available for delivery” if the quantity ordered is available on the
total stock of the two warehouses (i.e. quantity ordered <Stock Port0 + Stock
Lisboa)
● “Unavailable” if the quantity ordered is greater than the sum of the stock in
warehouses


Create an availability function that accepts the following arguments○ Material code (cell A) - the material code to be analyzed○ Location - a Warehouse Location String (cells H, I). It can be “Porto” or"Lisbon"○ Quantity - an integer with the quantity to order.The function should return one of three possible results:● “Available for immediate delivery” if the warehouse is locatedhave the quantity in stock.● “Available for delivery” if the quantity ordered is available on thetotal stock of the two warehouses (i.e. quantity ordered <Stock Port + StockLisbon)● “Unavailable” if the quantity ordered is greater than the sum of the stock inwarehouses

[Paul_Hossler]

Try this




Option Explicit


'Create an availability function that accepts the following arguments
' Material code (cell A) - the material code to be analyzed
' Location - a Warehouse Location String (cells H, I). It can be “Porto” or "Lisbon"
' Quantity - an integer with the quantity to order.


'The function should return one of three possible results:
' “Available for immediate delivery” if the warehouse is located have the quantity in stock.
' “Available for delivery” if the quantity ordered is available on the
' total stock of the two warehouses (i.e. quantity ordered <Stock Port0 + Stock Lisboa)
' “Unavailable” if the quantity ordered is greater than the sum of the stock in warehouses




Function Availablity(Mtl As String, Loc As String, Qty As Long) As String
Dim rowMtl As Long, colLoc As Long, Qty1 As Long, Qty2 As Long


With Application.Caller.Parent
rowMtl = 0
On Error Resume Next
rowMtl = Application.WorksheetFunction.Match(Mtl, .Columns(1), 0)
On Error GoTo 0

If rowMtl = 0 Then
Availablity = ""
Exit Function
End If

Qty1 = .Cells(rowMtl, 8).Value ' Porto
Qty2 = .Cells(rowMtl, 9).Value ' Lisboa
End With


If Loc = "Porto" And Qty <= Qty1 Then
Availablity = "Available for immediate delivery"
ElseIf Loc = "Lisboa" And Qty <= Qty2 Then
Availablity = "Available for immediate delivery"
ElseIf Qty <= (Qty1 + Qty2) Then
Availablity = "Available for delivery"
Else
Availablity = "Unavailable"
End If


End Function