+ Reply to Thread
Results 1 to 4 of 4

Help with VBA to Select next available quantity of stock. (FIFO)

  1. #1
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Help with VBA to Select next available quantity of stock. (FIFO)

    Hi All,

    Can anyone point out where i am going wrong:

    I have a Stock Table that when new Stock is received it is moved to working Table: Example:
    Please Login or Register  to view this content.
    1. Stock is moved to this working table when booked in on the main Table. A2:An (Oldest Stock first) B2 is the Available stock from A2.
    2. When stock is sold or deleted due to expiry Date in main Table it appears in the working Table in C2

    I have a Macro that Takes the Book Out Amount from the Available amount, When the available amount is insufficiant to meet the Book Out Amount the it books out the Available amount to 0 and the rest from B3 which is the next oldest.

    My problem is that it only does this for B2 -> B3 and then goes to minus amount in B3 and not into B4.

    How can i ammend this macro so once a Quantity is 0 it moves down through the Available Quantities each in turn?

    The Macro Code is as Follows:

    Please Login or Register  to view this content.
    Any Help would be greatly appreciated.

    Regards

    JRidge

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Help with VBA to Select next available quantity of stock. (FIFO)

    Hi,

    Maybe like this ? I also attach the file for you to test.

    Please Login or Register  to view this content.
    If you think this code is correct, then delete the red line and uncomment the green line.

    Regards
    Attached Files Attached Files
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Help with VBA to Select next available quantity of stock. (FIFO)

    Hi karedog,

    Looks good, Thank you very much.

    I will test it with my data

    Regards

    JRidge

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Help with VBA to Select next available quantity of stock. (FIFO)

    You're welcome JRidge and thanks for the reputation points.
    Please mark this thread as Solved, thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ***FIFO Closing Stock***
    By [email protected] in forum Access Tables & Databases
    Replies: 1
    Last Post: 10-05-2014, 04:49 PM
  2. Stock Aging- FIFO method
    By wschai in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-11-2013, 03:43 AM
  3. FIFO Stock Profit
    By dandan9999 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2013, 02:43 PM
  4. Inventory stock / PROFIT using FIFO
    By pdo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-09-2012, 02:46 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1