Macro to subtract based on two criteria, and loop

I have two sheets, sheet1 and sheet2.

I have trying to come up with a macro that loops through each row in sheet1 and subtracts sheet1!I from sheet2!F if Sheet2!A2=Sheet1!E2 & Sheet2!D2=Sheet1!H2 until 0 and then find the next row in sheet2 where Sheet2!A2=Sheet1!E2 & Sheet2!D2=Sheet1!H2 until there is 0 remaining on sheet1.

Hope the attachment helps.

Note, some items on sheet1 will not correlate with anything on sheet2

See if the procedure below will do the trick, it seemed to work on your sample:

Please Login or Register  to view this content.

Thanks CAntosh,

This seems to be working perfectly! I do have a few more questions though, if you would be so kind.

Is there anyway we can set it so it is not case sensitive? I have a few instances where capitalization isn't allowing the locations or items to match correctly, or where one sheet will have a trailing space at the end and the other does not.

I have to remove product description from Sheet1!H ; I currently use

Formula:

Please Login or Register  to view this content.

in a cell and drag though, then copy and paste over the original Sheet1!H. (It removes everything after the space. So original output is 3856 (Plated Kitchen Sink) the formula gives me 3856, I copy all and past over.

Could we have the results on sheet2!H instead of replacing sheet2!F , or create a copy of sheet2F! and paste into Sheet2!H prior to running the macro to subtract sales?

How could I add this formula

Formula:

Please Login or Register  to view this content.

into cell Sheet2!K2 and drag all the way through. (This is the sold quantity for a reference) -- not required.


Is it possible to add a macro above this? I have another macro I run prior that is a mass find and replace so the locations match on each sheet. (Our output record(sheet1) uses different names for location than the order sheet))

I would do this all manually, but I am afraid the end user would not be able to follow. (They are not very good with excel in general)

Again, thank you for your support it is a great help!

Alright, new version:

Please Login or Register  to view this content.

Putting "Option Compare Text" at the start of the module should fix the case-sensitivity issue. The problem with leading/trailing spaces can best be solved by wrapping your earlier formulas in a "TRIM" function, e.g.:

=TRIM(LEFT(H4,FIND(" ",H4&" ")-1))

The new version should leave Sheet2, column F alone and create a new quantity total in column H. It should also add your SUMIFS formula to column K.

I don't see any issue with this sub procedure following another, as long as everything in your workbook is in the right place before this procedure runs. You can either call UpdateQty from your first macro or you can paste my code within your other procedure, just be sure that the Option Compare Text is at the very top and make sure you don't have any overlapping definitions with the first part of the process.

Give it a go, let me know if you have any issues...

Thanks again cAntosh,

It appears to be working as hoped. I have integrated all the other features together and appears to work flawlessly.

Last question if I remove

Please Login or Register  to view this content.

from the end I shouldn't expect any issues should I? Currently just using to illustrate how the macro is working without flipping back and forth between sheets.

Glad I could help! All that last line does is add the formula you requested in post #3 to column K of sheet 2. If you don't need the formula there (or prefer to do it without VBA), then removing the line won't affect anything else.