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:

Sub UpdateQty()
Dim LR1 As Long
Dim LR2 As Long
Dim Remainder As Long
Dim i As Long
Dim j As Long

Application.ScreenUpdating = False
LR1 = Sheets(1).Cells(Rows.Count, "H").End(xlUp).Row
LR2 = Sheets(2).Cells(Rows.Count, "D").End(xlUp).Row
For i = 2 To LR1
    Remainder = Sheets(1).Cells(i, "I").Value
    For j = 2 To LR2
        If Sheets(1).Cells(i, "E").Value = Sheets(2).Cells(j, "A").Value And Sheets(1).Cells(i, "H").Value = Sheets(2).Cells(j, "D").Value And Sheets(2).Cells(j, "F").Value > 0 Then
            If Remainder <= Sheets(2).Cells(j, "F").Value Then
                Sheets(2).Cells(j, "F").Value = Sheets(2).Cells(j, "F").Value - Remainder
                Exit For
            Else
                Remainder = Remainder - Sheets(2).Cells(j, "F").Value
                Sheets(2).Cells(j, "F").Value = 0
            End If
        End If
    Next j
Next i
Application.ScreenUpdating = True
End Sub

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:

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

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:

=IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!I:I,Sheet1!E:E,A2,Sheet1!H:H,D2))

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:

Option Compare Text

Sub UpdateQty()
Dim LR1 As Long
Dim LR2 As Long
Dim Remainder As Long
Dim i As Long
Dim j As Long

Application.ScreenUpdating = False
LR1 = Sheets(1).Cells(Rows.Count, "H").End(xlUp).Row
LR2 = Sheets(2).Cells(Rows.Count, "D").End(xlUp).Row
Sheets(2).Range("F1:F" & LR2).Copy Destination:=Sheets(2).Range("H1")
For i = 2 To LR1
    Remainder = Sheets(1).Cells(i, "I").Value
    For j = 2 To LR2
        If Sheets(1).Cells(i, "E").Value = Sheets(2).Cells(j, "A").Value And Sheets(1).Cells(i, "H").Value = Sheets(2).Cells(j, "D").Value And Sheets(2).Cells(j, "H").Value > 0 Then
            If Remainder <= Sheets(2).Cells(j, "H").Value Then
                Sheets(2).Cells(j, "H").Value = Sheets(2).Cells(j, "H").Value - Remainder
                Exit For
            Else
                Remainder = Remainder - Sheets(2).Cells(j, "H").Value
                Sheets(2).Cells(j, "H").Value = 0
            End If
        End If
    Next j
Next i
Sheets(2).Range("K2:K" & LR2).Formula = "=IF(OR(A2="""",D2=""""),0,SUMIFS(Sheet1!I:I,Sheet1!E:E,A2,Sheet1!H:H,D2))"
Application.ScreenUpdating = True
End Sub

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

Sheets(2).Range("K2:K" & LR2).Formula = "=IF(OR(A2="""",D2=""""),0,SUMIFS(Sheet1!I:I,Sheet1!E:E,A2,Sheet1!H:H,D2))"

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.