inv - demand = excess - vba to calculate

[cmcconna]

Morning/ afternoon all

Looking for some help in created a excess statement. I have multiple parts in inventory at different dates and I have a demand statement. I want to automatically reduce the inventory based on demand to give me excess.

The probelm is I have mutiple Inventory of the same part number and I need to count each one down until demand is covered - if that makes sense


for example if I have part number A with 3 entires qty: 1, 5 , 9 and a demand of 2 : the excess for that should read 0, 4, 9


Appreciate if anyone could help.

Thanks

P.S couldnt attach sample file

https://www.excelforum.com/attachmen...1&d=1528979536

[MarMo]

Did you have a problem uploading a file?
Perhaps it was too big.
You should make a sample file with a few records, the problem and the expected result in a certain cell.
A sample workbook could do so much more if help is needed.

[cmcconna]

Yeah I have that for reference, however when I clicked on the attachment button only a long blank box appeared. so I couldnt upload it.

[davsth]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[cmcconna]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

File there now cheers

[cmcconna]

Any help on this one would eb much appreciated.. Thanks

[MarMo]

cmcconna,

I hope i found a solution to your problem.
I attached your modified file with extra VBA code.

I added a function from CPearson to find all matching cells within a range.

http://www.cpearson.com/excel/findall.aspx

And i took the testcode also from Cpearson to get things working.

See the code in the VBE editor.

Test the file by changing the values in cells F2:F5.
The result should be as requested.
THe only thing you should think about is, when a demand is greater then the parts stock.

Hope this helps.

[cmcconna]

MarMo - Many Thanks

Tried running the script but getting a " Method Range Of Object_Global failed" error when it reached

Please Login or Register  to view this content.

TargetCell is declared as public so Im not sure why this isnt running?

Please Login or Register  to view this content.

[jindon]

UDF

C2
=Excess(A2,$E$1:$F$5,A$2:B2)

and copy down

To a Standard Module

Please Login or Register  to view this content.

[cmcconna]

Thanks guys for the info.