| OrderNo | Account | Name | Code | Description | Quantity |
| 1 | 22 | Ex1 | APPLE1 | Apples | 17 |
| 2 | 38 | Ex7 | PEAR1 | Pears | 56 |
| 3 | 41 | Ex2 | ORANG1 | Oranges | 180 |
| 4 | 52 | Ex8 | LEMON1 | Lemons | 100 |
| 5 | 38 | Ex7 | PEAR1 | Pears | 250 |
| 6 | 22 | Ex1 | APPLE1 | Apples | 250 |
<tbody>
</tbody>
| Invoice | Account | Name | Code | Description | Quantity |
| 1 | T1 | Tom | ORANG1 | Oranges | 25 |
| 2 | S1 | Simon | APPLE1 | Apples | 25 |
| 3 | S2 | Sally | PEAR1 | Pears | 50 |
| 4 | D1 | Dot | LEMON1 | Lemons | 10 |
| 5 | K1 | Keith | APPLE1 | Apples | 50 |
| 6 | G1 | Gary | PEAR1 | Pears | 25 |
<tbody>
</tbody>
Hi, I need a little assistance. I have two tables on separate worksheets. Table 1 is purchases (Worksheet("Purchases")) & Table 2 is sales (Worksheet("Sales").
Code:
Sub Match1()
Dim rCl As Range
Dim Rw As Long
Dim Amt As Long
Dim sFind As String
Dim ws As Worksheet
Set ws = Worksheets("Purchases")
With Range("Sales") 'THIS IS THE NAMED RANGE OF THE SALES TABLE
For Rw = 1 To .Rows.Count
sFind = .Cells(Rw, 4).Text 'FINDS THE PRODUCT CODE
Amt = .Cells(Rw, 6).Value
On Error Resume Next
With ws.UsedRange.Columns(4)
Set rCl = .Find(sFind, LookIn:=xlValues, lookat:=xlWhole)
If Not rCl Is Nothing Then rCl.Offset(0, 2).Value = rCl.offset(0, 2).value - Amt
End With
Next Rw
End With
End SubThis code deducts the quantity of stock from the purchase when it's sold. But what I need is:- If the quantity of a sale is greater than the quantity in the purchase then it deducts the remaining quantity from the next purchase with that code.
Can anyone help me with this?
Thanks In Advance
