match data between two sheets based on four columns instead one column
hi
I have this code works very well . it match data between two sheets based on COL A and update the quantity in sheet inventory after matching it will subtracting the value in COL E from sheet sales .
so what I want update the code to become matching based on COL B,C,D,E together and update the quantity in COL F in sheet INVENTORY as in the code
NOTE: the structure into two sheets are the same thing
sheet sale
hi
I have this code works very well . it match data between two sheets based on COL A and update the quantity in sheet inventory after matching it will subtracting the value in COL E from sheet sales .
so what I want update the code to become matching based on COL B,C,D,E together and update the quantity in COL F in sheet INVENTORY as in the code
NOTE: the structure into two sheets are the same thing
VBA Code:
Sub qtyin()
Dim X, cell As Range
With Sheets("Sales")
For Each cell In .Range("A20:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
With Sheets("Result")
X = Application.Match(cell, .Range("A:A"), 0)
If Not IsError(X) Then
.Range("E" & X) = .Range("E" & X).Value - cell.Offset(, 4).Value
End If
End With
Next cell
End With
End Sub
sheet sale
update q.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
19 | item | cct | brand | type | origin | quantity | ||
20 | 1 | c-food | tune | 180wg | thi | 1 | ||
21 | 2 | c-wat | tune | 180wg | indo | 2 | ||
22 | 3 | c-food | tune | 180wg | indo | 1 | ||
SALES |
update q.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | item | cct | brand | type | origin | quantity | ||
2 | 1 | c-food | tune | 180wg | thi | 234 | ||
3 | 2 | c-wat | tune | 180wg | indo | 123 | ||
4 | 3 | c-food | tune | 180wg | indo | 200 | ||
inventory |