Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Reduce stocks of items by 1 when a button is hit

    Hi all,
    I have a spreadsheet which i want to use to manage a production process. I just created a spreadsheet attached to this message and i want to be able to make the operation defined below;

    - 'production' sheet is where i will enter the materials will be used

    when i hit a button,

    - 'stock' sheet is where i want to lookup all the material names and substract the value in column B from the value in column C

    by doing this, i will be able to reduce the stock quantity by the standard usage in column B.

    Can anyone help me building the code and learn about this very important operation in excel?
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Guys, i'd really appreciate help with this.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    lerq,

    Try this following code in a standard module and run from a form button placed on Production sheet. It does not matter if the types in the stock sheet are of a different order than the production sheet.

    HTH,
    Maud

    lerq1.png

    Code:
    Public Sub AdjustStock()
    '-------------------------
    'DECLARE AND SET VARIABLES
    Dim LastRow As Long, EndRow As Long, I As Long, num As Long
    '-------------------------
    'MATCH PACKING TYPE AND RETURN ADJUSTED STOCK
    With Worksheets("Stock")
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    EndRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For I = 2 To LastRow
        num = WorksheetFunction.Match(Cells(I, 1), .Range("A2:A" & EndRow), 0) + 1
        Cells(I, 2) = .Cells(num, 3) - .Cells(num, 2)
    Next I
    End With
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2015-05-31 at 23:57.

  4. #4
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Maud,
    This one works well but i will need to use the button again around a hundred times in a day. Each time i need to find these items in the stock sheet and reduce them by the values in column B in production sheet.
    Each time i use the button, the values should be decreased from the current values.
    Also, the items in production column A would be different each time, so i need to work this like a lookup and offset to find the current stock and decrease the values.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Each time i need to find these items in the stock sheet and reduce them by the values in column B in production sheet.
    lerq,

    Here are the adjustments you need. Like my first post, it will perform a search for the packing type, however, it will keep running totals for the deductions in Column B on the Stock sheet.

    HTH,
    Maud

    Code:
    Public Sub AdjustStock()
    '-------------------------
    'DECLARE AND SET VARIABLES
    Dim LastRow As Long, EndRow As Long, I As Long, num As Long
    '-------------------------
    'MATCH PACKING TYPE AND RETURN ADJUSTED STOCK
    With Worksheets("Stock")
    On Error GoTo errorhandler
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    EndRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For I = 2 To LastRow
        num = WorksheetFunction.Match(Cells(I, 1), .Range("A2:A" & EndRow), 0) + 1
        .Cells(num, 2) = .Cells(num, 2) - Cells(I, 2)
    errorhandler:
    Next I
    End With
    End Sub
    Attached Files Attached Files

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Maud

    Have you thought about using a column INDEX/MATCH with a PasteSpecial Operation:=xlSubtract

    zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    lerq (2015-06-02)

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Hi Zeddy,

    From a formula perspective within Excel, I would use index/match and do frequently. However from a vba perspective, an index/match can get messy and seems to be overkill for such simple coding. I know the PasteSpecial operation is one of your expertises and you use it well. I invite you to employ it here if you like.

    For me, simple is good and easy to debug

    Maud

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    lerq (2015-06-02)

  10. #8
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Maud. This second version solves my problem at hand.

Posting Permissions

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