| Order No | Account No | Name | Stock Code | Description | Quantity | Unit Cost | Net Amount | Tax Amount | Gross Amount | Order Date | Remaining Stock |
| 7 | 22 | Ex1 | APPLE1 | Apples | 500 | £0.50 | £12.50 | £2.50 | £15.00 | 10/07/2018 | 175 |
| 5 | 38 | Ex7 | PEAR1 | Pears | 200 | £1.50 | £37.50 | £7.50 | £45.00 | 17/07/2018 | 75 |
| 6 | 22 | Ex1 | APPLE1 | Apples | 250 | £1.00 | £250.00 | £50.00 | £300.00 | 17/07/2018 | 250 |
| 3 | 41 | Ex2 | ORANG/1 | Oranges | 180 | £3.00 | £540.00 | £108.00 | £648.00 | 15/08/2018 | 100 |
| 4 | 52 | Ex8 | LEMON1 | Lemons | 100 | £2.00 | £200.00 | £40.00 | £240.00 | 15/08/2018 | 80 |
| 2 | 38 | Ex7 | PEAR1 | Pears | 250 | £2.00 | £500.00 | £100.00 | £600.00 | 18/08/2018 | 250 |
| 1 | 22 | Ex1 | APPLE1 | Apples | 500 | £2.00 | £1,000.00 | £200.00 | £1,200.00 | 30/09/2018 | 500 |
<tbody>
</tbody>
| Invoice No | Account Ref | Type | Name | Stock Code | Description | Quantity | Selling Price | Net Amount | Tax Amount | Gross Amount | Invoice Date | Cost of Goods Sold |
| 8 | t1 | sale | tom | APPLE1 | Apples | 325 | £2.25 | £731.25 | £146.25 | £877.50 | 26/09/2018 | £162.50 |
| 7 | T2 | sale | Thomas | APPLE1 | Apples | 300 | £2.50 | £750.00 | £150.00 | £900.00 | 27/09/2018 | |
| 6 | G1 | sale | Gary | PEAR1 | Pears | 25 | £4.00 | £100.00 | £20.00 | £120.00 | 28/09/2018 | |
| 5 | K1 | sale | Keith | APPLE1 | Apples | 50 | £3.00 | £150.00 | £30.00 | £180.00 | 29/09/2018 | |
| 9 | T2 | sale | Thomas | APPLE1 | Apples | 10 | £2.50 | £25.00 | £5.00 | £30.00 | 30/09/2018 | |
| 4 | D1 | sale | Dot | LEMON1 | Lemons | 10 | £3.00 | £30.00 | £6.00 | £36.00 | 30/09/2018 | |
| 3 | S2 | sale | Sally | PEAR1 | Pears | 50 | £4.00 | £200.00 | £40.00 | £240.00 | 01/10/2018 | |
| 2 | S1 | sale | Simon | APPLE1 | Apples | 25 | £3.00 | £75.00 | £15.00 | £90.00 | 02/10/2018 | |
| 1 | T1 | sale | Tom | ORANG/1 | Oranges | 25 | £5.00 | £125.00 | £25.00 | £150.00 | 03/10/2018 |
<tbody>
</tbody>
Hi All,
Above is an example. There are 2 sheets, (Purchases) & (Sales). I've been attempting to get my macro
to calculate the cost price of a sale & update remaining stock but I keep hitting one stumbling block. Here's what I want to happen:
1. If the Cost of Goods Sold already has a value then go to next row.
2. If the Cost of Goods Sold is empty then .Find the stock code for the first purchase with remaining stock & calculate cost as follows
If the remaining stock is more than the qty sold then, (cost of goods sold = qty sold * unit cost) & the (remaining units = remaining units - qty sold)
If the remaining stock is less than the qty sold then, (cost of goods sold = remaining units * unit cost) & (qty = qty - remaining units) then
(remaining units = 0). Then .FindNext stockcode & (cost of goods sold = cost of goods sold + (qty * unit cost)). Then (remaining stock = remaining stock - qty)
Below is the code that I'm using but with my limited knowledge of Vba, whatever I try I just can't seem to get the cost prices into the correct cell
I really hope that you Excel Geniuses will be able to help me with this problem
Thanks in advance
ps, apologies if the code below requires some end ifs added/removed
Code:
Sub MatchSalesCostTest()
Dim NextRw As Long
Dim rCl As Range
Dim Rw As Long
Dim Qty As Long
Dim sFind As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Worksheets("Purchases")
Set ws2 = Worksheets("Sales")
With Range("Sales")
For Rw = 1 To .Rows.Count
sFind = .Cells(Rw, 5).Text
Qty = .Cells(Rw, 7).Value 'sales qty
If Not IsEmpty(.Cells(Rw, 13)) Then GoTo DoneFinding 'if cell is filled, goes to donefinding
With ws.Range("D1", ws.Cells(Rows.Count, "D").End(xlUp))
Set rCl = .Find(sFind, LookIn:=xlValues, lookat:=xlWhole)
Previous:
If rCl.Offset(0, 8).Value = 0 Then 'if remaining quantity is 0
Set rCl = .FindNext(rCl)
GoTo Previous
End If
If rCl.Offset(0, 8).Value < Qty Then 'if remaining qty is less than order qty
ws2.Cells(Rw, 13).Value = rCl.Offset(0, 8).Value * rCl.Offset(0, 3).Value 'DOES NOT WORK
Qty = Qty - rCl.Offset(0, 8).Value
rCl.Offset(0, 8).Value = 0 'remain qty = 0
Set rCl = .FindNext(rCl)
If rCl.Offset(0, 8).Value < Qty Then GoTo Previous
If rCl.Offset(0, 8).Value > Qty Then
rCl.Offset(0, 8).Value = rCl.Offset(0, 8).Value - Qty
ws2.Cells(Rw, 13).Value = ws2.Cells(Rw, 13).Value + (Qty * rCl.Offset(0, 3).Value) 'DOES NOT WORK
GoTo DoneFinding
End If
If rCl.Offset(0, 8).Value > Qty Then
rCl.Offset(0, 8).Value = rCl.Offset(0, 8).Value - Qty
ws2.Cells(Rw, 13).Value = Qty * rCl.Offset(0, 3).Value 'DOES NOT WORK
End If
DoneFinding:
End With
Next Rw
End With
End Sub
