Order/Inventory worksheet

Hi Excel gurus,

I hope someone can point me in the right direction.

I want to do the following... I have a table like in the picture.
I have orders for different products, some with only one line and some with multiple lines/items.
I have the quantity ordered and the quantity in stock for each item.

I look at the first order, 1111 and check the availability. We have enough in stock to ship the order.
Now I want to validate order 1111, meaning changing the color of this line to ‘green’ and more important, debit the used amount for every product in the ‘in stock’ column.

So for this example... I would enter 1111 and click on ‘validate’. This should put the 2 lines for 1111 in green and debit 50 from the ‘in stock’ column from every line with ‘Apple’ in the ‘item’ column. Same thing for the second item in this order; ‘Orange’.

Now I have no idea how to do part do debit the used quantity from the stock column for every item.
I appreciate any hint!


Capture.JPG

Hello gomb,

You will get more responses by posting your workbook. Most people are not inclined to recreate your what you have already done from a picture.


How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

I guess I need to loop through column B, finding all lines with 1111, looking up the item and qty ordered. After that look up any line containing the item and debit the stored amount from the stock column.
How can I do the loop and store the needed data in an array?

Thats what I have so far. I'm able to find each line but I dont know how to get the item and qty of the same row.

Please Login or Register  to view this content.

Thanks,

So, I have a little more now. I know how to do it and I'm on my way, but now I have a little issue.

Could someone tell me why the 2nd For loop is not finding the item? I dont get it!

Please Login or Register  to view this content.

Edit:
I know now what the issue is but I still dont know how to fix that.
I'm not getting the right cell address as the following line does not select the range. Why?

Please Login or Register  to view this content.

Thanks,