Fifo Inventory Valuation

Re: Fifo Inventory Valuation

See the FIFO Template here;

http://www.excel-it.com/workbook_downloads.htm

Re: Fifo Inventory Valuation

Roy,

I like your template, good work.

In the instructions (both in the workbook and on the Excel-it site), you refer to a sheet named Stock. The example has three sheets named (Explanation, FIFO, and Lists). The FIFO sheet does what is described as being on the sheet named Stock. Just thought you'd like to know.

Regards,

Re: Fifo Inventory Valuation

Thanks Brandtrock

I rushed it for this example, I will amend it. I will also add a LIFO Function

Re: Fifo Inventory Valuation

Hi Brian,

I played with the example code you posted but no luck yet, it simply doesn't work. But the good news is here's a working model of your sheet.

Notes:

I set the Column numbers as variables in the Subs. If you want to change the Columns around you must change the numbers in the Workbook Sheet_Change Macro and in the Recalc macro to account for the changes. They are clearly marked "CHANGE TO SUIT"

The Sheet Change Macro uses 3 Column numbers: "Units Received" and "Costs of Goods Received" and "Shipped"

The Recalc Macro uses 4 Column numbers: the three above plus "FIFO Valuation"

The Subs are basically automatic - require no named ranges, and work on ALL sheets. The only thing they require is an entry in the first row of data (currently row 3) in the Received Column.

Errors are to be expected, I suppose. Let me know!

Edit:

Presumes either an Rec'd value OR a Shipped value on each row. I didn't code for both as that's not how the example was layed out.

Re: Fifo Inventory Valuation

Hi bryz,

Re Changing price. I missed one reference in the Workbook Module. By which I mean I presumed start row of 3. There are two lines to add and one to change.

First Dim's a variable ("InRow") for the first line of data

Second sets the value of the variable (change to match your first row of data)

Third (change line) takes out the reference to row 3 as the first line of data and uses the variable instead.

See below:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    
Dim Recd As Long    'Column
Dim Costs As Long   'Column
Dim shipped As Long 'Column






'*********ADD THIS LINE**********
Dim InRow As Long   'First row of data
'********************************


'CHANGE TO SUIT: Also change in Sub Recalc
   
'*********ADD THIS LINE**********
    'First row of data (Change number to suit)
    InRow = 3
'********************************




<SNIP>
    
    'was Received/Cost/Shipped?


'***********************CHANGE 3 to InRow***
    If Not Intersect(Target, Range(Cells(InRow, Recd).Address, Cells(lastrow, shipped).Address)) Is Nothing Then
'                               ******
'************************************************




<SNIP>




End Sub

Re: Fifo Inventory Valuation

Well, I've been playing hard trying to break it but I can't get to freeze or get any errors at all.

Perhaps post a small example of your latest version?

My only note is that the Columns are defined in 2 places and perhaps I should change them to Public and have them defined in one place only so ensure they always match.

The WB Sub uses 3 cols and Recalc uses those 3 as well. There is no reason why they should cause a crash if not defined correctly but who knows? I'll try that now!

Edit: Nothing I've tried will get any event to fire. I'll wait for to post your version.

PS: This will toggle Events On/Off

Sub EventsOnOff()
    'Resets to opposite (if True then False, if False then True)
    Application.EnableEvents = Not Application.EnableEvents
'optional
    MsgBox (Application.EnableEvents)
End Sub

Re: Fifo Inventory Valuation

Hi bryz,

My mistake (presumption again...) I had the Rec'd/Cost/Shipped Columns in a certain order so the Sheet Change Sub was looking for them (as a range) in that order. What that means is neither the first version or the corrections I posted would work on your version. I didn't allow for the Columns to be in any other order.

This Change Sub looks at each Column instead of presuming they are in a certain order, so as long as the Recd, Costs and Shipped Column numbers are set correctly (as variables) it will find them wherever they are in any order. Much better.

As for the 'freezing' I manged to get the sheet to freeze by erroring the Change sub. All I could do was click to close Excel. But instead of saving I clicked Cancel in the dialog and I was back in the sheet and operational again. So, what I did was standardize the enable/disable Events and enable/disable Screenupdating in every macro (turning them both off at the beginning and both back on at the end of each and every macro). This is a slight bit of overkill but better safe and all. It _appears_ to have fixed the problem. Let me know.

Some other issues are noted as comments in the attached example. Mainly that the data validation doesn't work on cell changes caused by formulas so I put a check in the Change sub for negative inventory. Also the Adjust column wasn't allowing negative numbers and the data validation message read "Can be positive _OF_ negative".

I think that's it for this round...

Edit:

Copy all of the subs to (a copy) of your real sheet and test them out again if you will.

Re: Fifo Inventory Valuation

Hi,

Not quite sure I understand what you're saying. If a macro turns off the screen update it should turn it back on when done (some versions of Excel do it when the sub exits, some versions don't).

EnableEvents simply allows Change events (etc) to run or not. So if you are running a macro that will change anything on the sheet and you have Event code then the Macro should probably disable Events until it is finished then Enable them again when done. (There are exceptions to every rule including this one!)

Inserting/Deleting rows and or Columns should not be affected either way!

I guess is that if a macro turns off either or both and then calls another macro the second one shouldn't have to deal with the ones that the calling macro has already done.

The only solution I can think of is get the workbook working correctly. Then put a break in every Event sub and then run each macro to see which ones are firing event subs or other subs and which aren't. Then run each sub and after letting each sub finish see if the Row/column problem has returned. This should lead to where/how the problem is being caused?

Re: Fifo Inventory Valuation

Hi Bryz,

Can you post a non working version?

Re: Fifo Inventory Valuation

Better way

With Application 
        .ScreenUpdating = False 
        .EnableEvents = False 
        
     'add code here     
  
        .EnableEvents = True 
        .ScreenUpdating = True 
         
    End With 
     
End Sub

Re: Fifo Inventory Valuation

Hi,

Did we get there?

Quote

ALMOST there! Seems that everything is now working well with exception to one item

Happy?

Re: Fifo Inventory Valuation

thanxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Re: Fifo Inventory Valuation

Hi Roy,

I am not sure whether you are still at Excel It, but I just bought your template for FIFO and it shows error on List tab!!!! Please Help!!!! Thx

Re: Fifo Inventory Valuation

Hello pie131,

Welcome to Ozgrid.

Please do not make replies in other threads unless you are providing a solution.
There is contact link on Roy's web site. Use that link to obtain the support you need.
This forum is for asking specific Excel/VBA questions.

You may start a new thread, if desired, and ask for help on a specific topic, explaining clearly what your needs are. Threads are limited to a single topic with closely related follow up questions permitted.

When creating threads, please note:

Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words. That is, your title use as search terms would return relevant results.

• The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
• The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
• The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

Do not make any further replies in this thread.