+ Reply to Thread
Results 1 to 6 of 6

Running inventory control/User form

  1. 12-18-2013, 12:36 PM #1
    Rekoons38
    Rekoons38 is offline
    Registered User
    Join Date
    11-06-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    12

    Running inventory control/User form

    Hello, I am fairly new to VBA and have learned quit a bit lately, but no I am stuck. I have a workbook with 2 worksheets. Sheet 1 is a list of many parts and where I wish to have current inventory levels posted. Sheet 2 keeps a running log of transactions. I have created a user form to look up parts and edit transactions. transactions are posting to sheet 2 as they should however, I cannot figure out how to calculate a running "ON HAND" total.

    I have formulas in sheet 1 to pull information from sheet 2 as they change, but think I have created a mess. I am sure there is a much more efficient and reliable way to accomplish this task. I am attaching my work and any help would be greatly appreciated.

    SBS_PARTS_TEST.xlsm


    Rekoons
    Reply With QuoteRegister To Reply
  2. 01-06-2014, 09:20 AM #2
    Rekoons38
    Rekoons38 is offline
    Registered User
    Join Date
    11-06-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Running inventory control/User form

    Quote Originally Posted by Rekoons38 View Post
    Hello, I am fairly new to VBA and have learned quit a bit lately, but no I am stuck. I have a workbook with 2 worksheets. Sheet 1 is a list of many parts and where I wish to have current inventory levels posted. Sheet 2 keeps a running log of transactions. I have created a user form to look up parts and edit transactions. transactions are posting to sheet 2 as they should however, I cannot figure out how to calculate a running "ON HAND" total.

    I have formulas in sheet 1 to pull information from sheet 2 as they change, but think I have created a mess. I am sure there is a much more efficient and reliable way to accomplish this task. I am attaching my work and any help would be greatly appreciated.

    Attachment 284901


    Rekoons
    Was this post out of line?
    Reply With QuoteRegister To Reply
  3. 01-09-2014, 01:15 PM #3
    Rekoons38
    Rekoons38 is offline
    Registered User
    Join Date
    11-06-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Running inventory control/User form

    Ok, if we ignore the "add to inventory button, the formulas I have on sheet 1 will copy the first match from sheet 2. I just need them to copy the row based on the last match in column a. Anyone know how I can tweek those formulas instead of using VBA?
    Reply With QuoteRegister To Reply
  4. 01-09-2014, 02:27 PM #4
    gsnidow
    gsnidow is offline
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Running inventory control/User form

    Rekoons, if I might make a suggestion... You are going to cause yourself headaches by trying to maintain any transactional info on the same line as your master list items on sheet 1. You should have a sheet (table) for orders you receive, which add to your inventory balance, and a separate sheet (table), for where you send items to your customers, where the inventory balance is decremented. Then, in the most simplistic terms, the standing inventory count is simply the sum of your receipts minus the sum of your outflow. This value could very well be a field in your master list. This typically would be better suited to a database, but Excel will do just fine as long as you set it up in a relational manner. You should google something like "inventory database design", then model your Excel workbook accordingly. Just my two cents.


    Greg
    Just a guy trying to make work stuff easier.
    Reply With QuoteRegister To Reply
  5. 01-10-2014, 09:54 AM #5
    Rekoons38
    Rekoons38 is offline
    Registered User
    Join Date
    11-06-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Running inventory control/User form

    I agree Greg. Thanks for the input. This stuff can sometime make you overthink the situation!
    Reply With QuoteRegister To Reply
  6. 01-10-2014, 12:12 PM #6
    Rekoons38
    Rekoons38 is offline
    Registered User
    Join Date
    11-06-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Running inventory control/User form

    Ok, I have now set up individual sheets for receivables and charges. I tweaked the formulas on sheet 1 to pull information from appropriate columns on the 2 sheets. Now I still have an issue with getting it to pull the last entry that matches. Also as I have some parts that do not have a stock number, I am considering adding a button to the form to search by part number. This I can handle, but this information also will need to be updated on sheet one and will have nothing in column a.

    I have attached the modified file and would appreciate any input.

    SBS_PARTS_TEST_2.xlsm


    Thanks!
    Rekoons
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. pass control to user while running macro
    By CocoMoco in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2011, 02:30 AM
  2. [SOLVED] Finding elusive control on User Form
    By Don Wiss in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2006, 09:10 PM
  3. Adding a control to a User Form
    By scantor145 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-17-2005, 05:05 PM
  4. user form & control properties
    By Mike Molyneaux in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-01-2005, 04:41 PM
  5. [SOLVED] user form and frame control
    By Gixxer_J_97 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2005, 07:06 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1