stock control?
Posted by MAC on August 20, 2001 5:16 AM
on a worksheet used for stock records i have got 5 cloumns. columnA and B are used for descriptions and product codes,column C is for number of items used that day and column D is for number of items made that day,column E shows the total now in stock.
What i want to do is have the total stock column adjusted by the ammount that gets entered into column c (used) or column D (made).Also when the sheet is activated I need the contents of C and D clearing ,ready for the next input of figures but without the contents of column E (total stocks) being affected I suppose this ammounts to a simple stock controll system?
Is this possible or am I asking too much??
Many thanks
MAC
Posted by Rob Jackson on August 20, 2001 7:32 AM
MAC,
You could set it so that when opening a spreadsheet those columns are cleared and you could switch calculation to calc on exit so nothing recalcs until you close the spreadsheet. Calculation is done in the Tools - Options menu. This column thing requires a line of code in the workbook_open module
Columns("C:D").ClearContents
However this could cause problems in the event of the workbook being closed before all products have been updated.
Might want to look at a third column like a previous balance column which each time you open the workbook it copies your stock column to the previous balance column as a value (So won't recalc) then clears the C and D columns ready for new figures. Your stock figure would need to become Prev + Made - Used.
If F became youyr previous ballance column then put this in your workbook_open module
Columns("E:E").Copy
Columns("F:F").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Columns("C:D").ClearContents
Just a thought.
Are we looking at more than one update a day? Do all figures get updated each day. Do you need to store back records so you can track usage over time? There are other options...
