Consulting

Results 1 to 2 of 2

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

  1. #1

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

    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
    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •